Creating group subdivisions using calculated columns

[Luke George 2]

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!

[Sean Riley]

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.

[Luke George 2]

Thanks Sean!