Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ];
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