compute sum but reset it to zero depending value

[LECOMTE FRANCOISE 2]

I have the following file

Date-Time Equipment Interval Cumul I can do Cumul I Want ID Ligne

01/01/2020 05:00 1 1 1 1

01/01/2020 05:10 1 10 11 11 2

01/01/2020 05:11 0 1 0 0 3

01/01/2020 05:20 0 9 0 0 4

01/01/2020 05:25 0 5 0 0 5

01/01/2020 05:30 1 5 16 5 6

01/01/2020 05:35 1 5 21 10 7

01/01/2020 05:40 1 5 26 15 8

01/01/2020 05:45 1 5 31 20 9

01/01/2020 05:46 0 1 0 0 10

01/01/2020 05:56 0 10 0 0 11

01/01/2020 06:06 0 10 0 0 12

The cumul I can have is done by :If([Equipment]>0,

If(first([Equipment]) over (previous([iD Ligne]))=0,[intervalle] * [Equipment],

Sum([Equipment] * [intervalle]) OVER (intersect([Date],previous([iD Ligne]))) + ([Equipment] * [intervalle])))

The idea is to cumul minutes and be able to have the total of minutes of using. If the equipment is reset to 0 (switch off) then after several minutes switch on (value=1), I want to restart a new cumul from this point

I'm not sure it's possible in spotfire , any idea

[Fabian Duerr]

I was thinking about this for some time but I can't come up with a simple solution that uses only a calculated column.

 

My approch would be to first group the data point. So when you can come up with something that tells you that line 1-2 are run1, and line 3-5 are run2, and 6-9 are run3,... Then you could easily use the cumulative sum for each run with the condition Equipment = 1.

 

Calculating those run IDs should be straight forward with a data function. Because you just need a counter that increases by one everytime the entry in Equipment entry changes. I just don't know how to implent this with a calculated column. Maybe someone else has a clever solution for this.

[Gaia Paolini]

I think I can find some solution with two intermediate columns.

 

Based on the answer to this question:

https://community.spotfire.com/questions/successive-numbering-data-groups-using-calculated-columns

 

1 - first define a column that increases every time Equipment returns to 1: call it [EquipmentChange]

 

If(([Equipment]=1) and (([iDLigne]=1) or (([Equipment] - Max([Equipment]) over (previous([iDLigne])))=[Equipment])),1,NULL)

 

2 - then compute your equipment grouping column, call it [EquipmentIndex]

If([Equipment]=1,Integer(Rank([Equipment]) - 1 + Sum([EquipmentIndex]) over (allprevious([iDLigne]))),NULL)

so now instead of being 0,1,0,1 etc it increments as 0,1,0,2,...

 

3- then compute your final sum, adding an Intersect with [EquipmentIndex] to the basic formula:

case

when [Equipment]>0 then Sum([interval]) over (Intersect([EquipmentIndex],AllPrevious([iDLigne])))

else 0

end

 

See answer to your previous question for the basic formula without Intersect:

https://community.spotfire.com/questions/compute-sum-depending-column-value

 

I am not sure [interval] is correct for the first row, but maybe you had previous rows you are not showing. I would have calculated it as

SN(DateDiff("minute",Max([Date-Time]) OVER (Previous([iDLigne])),[Date-Time]),0)

Which would have made it 0 for the first row.

[LECOMTE FRANCOISE 2]

Thanks a lot for the solution with the 3 caclated columns, it works perfectly