Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Wondering if anyone has a "simple" way to create the score indicator below.
Example:
jan | feb | mar | apr | may | jun | jul | aug | sep | oct | Score |
31 | 38 | 38 | 56 | 20 | 99 | 100 | 98 | 96 | 94 | -3 |
Above.... the Score of -3 indicates that for the past 3 months the score has been going down.
i.e. from July onwards the trend has been consecutively down. (100 > 98 > 96 > 94)
In the code is there a way anyone can think of doing this? I was thinking of using the previous function somehow but it starts to get messy quite soon so any thoughts are appreciated.
Thank you.
There is an example for exactly this on
https://community.qlik.com/t5/Qlik-Design-Blog/Counters-in-the-Load/ba-p/1464117
Try
Data:
Load
If(Trend=Peek(Trend),Peek(Score)+Trend,Trend) as Score,
*;
Load
If(Change=0,Peek(Trend),Sign(Change)) as Trend,
*;
Load
ID,
Month,
Value,
Value - Peek(Value) as Change
Resident SourceData
Order By Month;
Let Score = Peek('Score',-1,'Data');
There is an example for exactly this on
https://community.qlik.com/t5/Qlik-Design-Blog/Counters-in-the-Load/ba-p/1464117
Thanks Henric,
I've had a play around with the code here but still not 100% sure on how to implement counters if negative as well (it seems to only account for positive scores). i.e. if months jan - mar were increasing, it would be +2, but then from Mar - Jun if they were decreasing I should see -3.
Try
Data:
Load
If(Trend=Peek(Trend),Peek(Score)+Trend,Trend) as Score,
*;
Load
If(Change=0,Peek(Trend),Sign(Change)) as Trend,
*;
Load
ID,
Month,
Value,
Value - Peek(Value) as Change
Resident SourceData
Order By Month;
Let Score = Peek('Score',-1,'Data');