Code to calculate consecutive negative or positive monthly values

[cb5981]

Hi all, 

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

1. Each month has a score assigned to it. 
2. 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.

[hic · Accepted solution]

There is an example for exactly this on 

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

[cb5981]

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.

[hic · Accepted solution]

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