Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
alis2063
Creator III
Creator III

Discrete QTRs data to be added

Need to subtract the current value from previous value in Set Analysis

below is Actual value & expected output against the quarters 

 

Quarter Value Expected Output
Q1 30 30
Q2 90 60
Q3 120 30
Q4 180 60

 

 

Regards,

Ali

Labels (1)
1 Solution

Accepted Solutions
marksouzacosta

Hi @alis2063,

You don't need Set Analysis. You can do this changing your Load Script. See the code below:

TempData:
LOAD * INLINE [
Quarter,Value
Q1,30
Q2,90
Q3,120
Q4,180
];

Data:
LOAD
    Quarter,
    Value,
    Value - Alt(Peek(Value),0) AS Expected
RESIDENT
	TempData
ORDER BY
	Quarter
;

DROP TABLE TempData;

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

View solution in original post

4 Replies
marksouzacosta

Hi @alis2063,

You don't need Set Analysis. You can do this changing your Load Script. See the code below:

TempData:
LOAD * INLINE [
Quarter,Value
Q1,30
Q2,90
Q3,120
Q4,180
];

Data:
LOAD
    Quarter,
    Value,
    Value - Alt(Peek(Value),0) AS Expected
RESIDENT
	TempData
ORDER BY
	Quarter
;

DROP TABLE TempData;

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

alis2063
Creator III
Creator III
Author

Can't we do this in set analysis

marksouzacosta

hi @alis2063,

Maybe it is possible to do using Set Analysis, but Qlik have other features to better handle this scenario.
For a chart table for example you can use the Above function:

Sum(Value) - Alt(Above(Sum(Value)),0)

marksouzacosta_0-1721925157357.png

Where are you trying to use that expression?

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

alis2063
Creator III
Creator III
Author

I have tried the same set analysis unfortunately not working for me