Lag function calculation on summrized data?

[#1]

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

Month	Count
Jan	18729	0
Feb	19939	1210
Mar	22692	2753
Apr	20251	-2441
May	19138
Jun	10241
Jul	7752
Aug	12987
Sep	23788
Oct	24358
Nov	24518
Dec	21943

[whiteline · Accepted solution]

Hi.

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

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

[#3]

Thanks. Worked perfectly!

[#4]

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]

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().

[#6]

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]

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