Counting days in a row in a table based on threshold?

[Sazabi]

Hi hello

I am wanting to display the number of consecutive days in a table when a KPI has passed a certain threshold.

Example:

Count # of days when KPI value is >=10

Therefore, on 2019-01-04, the KPI was >=10 for 2 days in a row.

On 2019-01-15, the KPI was >=10 for 6 days in a row,  7 the next day, etc.

I've tried putting in a 0 | 1 flag for this in the load script and doing things with rangesum()

ex: 

rangesum(above(sum(KPI_flag),0, if(above(sum(KPI_flag)) <> sum(KPI_flag),1,rowno()) ))

But it is not getting me where I want.

Load Script I used:

[Original]:
load * INLINE [ pst, KPI
2019-01-01, 1
2019-01-02, 1
2019-01-03, 12
2019-01-04, 20
2019-01-05, 11
2019-01-06, 2
2019-01-07, 90
2019-01-08, 50
2019-01-09, 1
2019-01-10, 20
2019-01-11, 21
2019-01-12, 22
2019-01-13, 23
2019-01-14, 24
2019-01-15, 25
2019-01-16, 27
2019-01-17, 0
2019-01-17, 9
];

[Final]:
NoConcatenate Load pst, KPI,
if(KPI >=10, 1, 0) as KPI_flag
Resident [Original];

drop table [Original];
exit script;

Have you guys done anything like this before?

Thanks!!

S

[Vegar]

Try this script. 

[Original]:
load * ,if(KPI >=10, peek('KPI_flag')+1, 0) as KPI_flag 
INLINE [ 
pst, KPI
2019-01-01, 1
2019-01-02, 1
2019-01-03, 12
2019-01-04, 20
2019-01-05, 11
2019-01-06, 2
2019-01-07, 90
2019-01-08, 50
2019-01-09, 1
2019-01-10, 20
2019-01-11, 21
2019-01-12, 22
2019-01-13, 23
2019-01-14, 24
2019-01-15, 25
2019-01-16, 27
2019-01-17, 0
2019-01-17, 9
];

[Sazabi]

Hi Vegar, 

Very cool solution 🙂

Now to take it to the next level:

Is there a way to do this calculation live on the front-end rather than hard-code the values into the data model?

In the case of having the timestamps broken out by dimensions, I can see we could sort by time and dimension and then include in the if-statement to check if the previous record is the same object and do the count. 

Thoughts?

S