Based on user input sum grouped rows and then find difference between each subsequent value

[JonesBeach]

Hi all,

I am trying to formulate an I-MR Chart in Qlik. When user selects a state from the state picklist. I want to add all the "Avg Weather" by year for the corresponding states and then find the row wise difference between each subsequent row. for e.g

if user picks NY, CA and TX then for the three years in data the sum will be 68, 69 and 56 respectively and the difference for each row will be 68-69 = -1 then 69-56= 13. in original data set I have over 50 rows by year-month and all 50 states. This would be extremely helpful. I need this in expression so I can use it in the upper and lower limits for I-MR chart limits. 

States	Year Month	AVG_Winter_Weather
NY	2020-12	15
DC	2020-12	18
CA	2020-12	28
IL	2020-12	16
TX	2020-12	25
NY	2020-01	13
DC	2020-01	17
CA	2020-01	29
IL	2020-01	12
TX	2020-01	27
NY	2020-02	14
DC	2020-02	14
CA	2020-02	22
IL	2020-02	17
TX	2020-02	20

[justISO · Accepted solution]

Hi, maybe it is easier to approach like this - just make a measure expression of sum(AVG_Winter_Weather), but just add modifier ('Difference') to it. This way you probably will get what you need here. In addition, under 'Output expression' you can find expression how result is got and can reuse it too:

[ajaykakkar93 · Accepted solution]

Hi,

data shared is

data:

//step 1
load States,Year_Month,AVG_Winter_Weather;
load * Inline [
States,Year_Month,AVG_Winter_Weather
NY,2020-12,15
DC,2020-12,18
CA,2020-12,28
IL,2020-12,16
TX,2020-12,25
NY,2020-01,13
DC,2020-01,17
CA,2020-01,29
IL,2020-01,12
TX,2020-01,27
NY,2020-02,14
DC,2020-02,14
CA,2020-02,22
IL,2020-02,17
TX,2020-02,20
];

exit Script;

Qlik front end add 
dimension : Year_Month
measure 1 : Sum(AVG_Winter_Weather)
measure 2 : Sum(AVG_Winter_Weather)-Above(Sum(AVG_Winter_Weather))

You will have your output using qlik front end 

[JonesBeach]

Thanks for you answer Ajay man, it works fine btw but I need the sum of those differences now. Which I either need as Master Item so I can use it where I originally wanted to or I need a KPI measure. PLease let me know if you can shine some light on that.

sum(Aggr(Fabs(Sum(AVG_Winter_Weather)-Above(Sum(AVG_Winter_Weather))), MonthYear))

this is what I got but idk really know why is it giving a different value then the actually sum. 

Regards,

Jabran