Jump to content
We've recently updated our Privacy Statement, available here. ×

Creating group subdivisions using calculated columns


Luke George 2

Recommended Posts

Suppose I wanted to create aculculated column that subdivides an initial column. The initial column aleady shows groups labeled as 1, 2, 3 etc with 0's seperating those groups.Thecalculated column would thensimply subdivide the initialgroups by a set integer, say 3, as in the below example. Is there a way to do this

 

 

 

Initial Column

Calculated Column

 

 

0

 

 

 

1

1

 

 

1

1

 

 

1

1

 

 

1

2

 

 

0

 

 

 

0

 

 

 

2

3

 

 

2

3

 

 

0

 

 

 

0

 

 

 

3

4

 

 

3

4

 

 

3

4

 

 

3

5

 

 

3

5

 

 

3

5

 

 

3

6

 

 

0

 

 

 

4

7

 

 

 

Thanks!

Link to comment
Share on other sites

This is not trivial at all, but could be done with a few calculated columns. There is grouping on the "initial column" value and then a hard coded limit of 3 before incrementing:

1. Select Data > Add...

Source: Data loaded from clipboard.

Data loaded at: 2/20/2019 9:10 AM

Data was added as a new data table

2. Data > Add calculated column...

Column name: Index

Expression: BaseRowId()

3. Data > Add calculated column...

Column name: Group

Expression: (If([initial Grouping]!=0,

Ceiling(Rank(BaseRowId(),[initial Grouping]) / 3))) - 1

4. Data > Add calculated column...

Column name: Extra Increment

Expression: If(Round((Rank(BaseRowId(),[initial Grouping]) / 3) - Floor(Rank(BaseRowId(),[initial Grouping]) / 3),2)=0.33,If([Group]!=0,1))

5. Data > Add calculated column...

Column name: Final2

Expression: If([initial Grouping]!=0,[initial Grouping] + Sum(SN([Extra Increment],0)) OVER (AllPrevious([index])))

This is a very particular implementation though (the "/3" and "=.33" would need to get updated if there were a different number of values in the subdivision), and if any followup is needed I think it would be better to understand your actual data set and the overall end result you are looking implement. Hope this helps.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...