Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Lag function calculation on summrized data?

Hi experts here,

        I would like to do the lag calculation for summarized data in Qlikview. Not sure how. Please advise.

        I can easily get the summaried data (see below) from original data, I would like to have a new column (new expression?) with the count differences between current month and the month before. In this case Feb - Jan, Mar-Feb and Apr - Mar and so on.

Thanks

MonthCount
Jan187290
Feb199391210
Mar226922753
Apr20251-2441
May19138
Jun10241
Jul7752
Aug12987
Sep23788
Oct24358
Nov24518
Dec21943
1 Solution

Accepted Solutions
whiteline
Master II
Master II

Hi.

You can use above() function to get count calculated for previous month:

=count(value)-above(count(value))

View solution in original post

6 Replies
whiteline
Master II
Master II

Hi.

You can use above() function to get count calculated for previous month:

=count(value)-above(count(value))

Not applicable
Author

Thanks. Worked perfectly!

Not applicable
Author

One more question. How to get sum and average of calculated value - count(value)-above(count(value)). I tried to use variable and aggreate function, still cannot get it to work. Thanks

whiteline
Master II
Master II

Hi.

You can't use avg(aggr(value-above(count(value)), Month)) because aggr() sorts the values by loadorder.

If you just want to calculate totals - use 'Total mode' (expression tab).

Otherwise depending on your datamodel you have to use another approach without interrecord functions like the above().

Not applicable
Author

Can I sort by dimension in order to be able to use ABOVE function? If not, what do I have to do? We do need to have a way to get moving range - the absolute value of the first difference (e.g., the difference between two consecutive data points) of the data. Any suggestions are highly appreciated.

Thanks

swuehl
MVP
MVP

Hi longmatch,

if I've understood your request correctly, you have at least two options:

a) Vote for http://community.qlik.com/ideas/1391 (and wait...)

b) in the meantime, you can try getting your load order right. Essentially, like the way I've posted here:

http://community.qlik.com/message/265894#265894

(wasn't this your post? 😉

Maybe I haven't got your request correctly, if so, please clarify.

Regards,

Stefan