topic Counting days in a row in a table based on threshold? in App Development

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

Sazabi — Tue, 12 Feb 2019 19:49:27 GMT

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

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

Vegar — Tue, 12 Feb 2019 21:59:14 GMT

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

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

Sazabi — Tue, 12 Feb 2019 22:36:24 GMT

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?