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

Cumulated sum when One column is Zero Or one


Go to solution Solved by Gaia Paolini,

Recommended Posts

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 thanksimage.png.5c788bbb1c4ba1faf9bba9605eeabbe3.png

 

 

Link to comment
Share on other sites

  • Solution

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])


 

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...