topic Re: Code to calculate consecutive negative or positive monthly values in App Development

Code to calculate consecutive negative or positive monthly values

cb5981 — Thu, 14 Apr 2022 06:51:05 GMT

Hi all,

Wondering if anyone has a "simple" way to create the score indicator below.

Each month has a score assigned to it.
The ask is to then provide a score indicator as to how many months the score has been going up or down consecutively.

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)

If the Scores had been consecutively going down since April the Score would be -6
If the scores had been going up since August, the score would be +2

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.

Re: Code to calculate consecutive negative or positive monthly values

hic — Thu, 14 Apr 2022 07:19:36 GMT

There is an example for exactly this on

https://community.qlik.com/t5/Qlik-Design-Blog/Counters-in-the-Load/ba-p/1464117

Re: Code to calculate consecutive negative or positive monthly values

cb5981 — Thu, 21 Apr 2022 16:14:16 GMT

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.

Re: Code to calculate consecutive negative or positive monthly values

hic — Mon, 25 Apr 2022 11:46:59 GMT

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');