Cumulated sum when One column is Zero Or one

[Cristian Tibrea]

Hello, I am trying to create a calculated column that will count the time (sec) when [Col1] is either 1 or 0. 

I tried Sum([Time (sec)]) over (Intersect(allPrevious([Row_ID]),[Col1])) but I get the CummSum continuous without starting from zero every time [Col1] changes. Here is a sample of the data. I want the result to be like CalcCol or CalcCol2. Many thanks

 

 

[Gaia Paolini]

I understand that you already have a [Row_ID] column which is calculated as RowId()

You don't say what [CalcCol2] is meant to represent, I assume it is the count of values in each group of [CalcCol1]

The idea is to create a duplicate of [Col1] that increments every time [Col1] changes.

Call it [Col1b] as:

Sum(Integer(SN(Lag([Col1]),[Col1])!=[Col1])) OVER (AllPrevious([Row_ID]))

where 

Lag([Col1]),[Col1])!=[Col1]

is True when [Col1] changes and False when it stays the same.

• SN(..) is there to set the first value to [Col1]
• Integer(..) turns True/False into 1/0

Then you calculate [CalcCol1] as:

DenseRank([Row_ID],[Col1b])

and [CalcCol2] as:

Max([CalcCol1]) over ([Col1b])

 

[Cristian Tibrea]

I would like to thank you Gaia, it was exactly what I wanted. Much appreciated!