topic Re: Lag function calculation on summrized data? in QlikView

Lag function calculation on summrized data?

Fri, 28 Sep 2012 19:32:03 GMT

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

Re: Lag function calculation on summrized data?

whiteline — Fri, 28 Sep 2012 19:50:35 GMT

Hi.

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

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

Re: Lag function calculation on summrized data?

Fri, 28 Sep 2012 19:59:57 GMT

Thanks. Worked perfectly!

Re: Lag function calculation on summrized data?

Mon, 01 Oct 2012 14:59:28 GMT

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

Re: Lag function calculation on summrized data?

whiteline — Mon, 08 Oct 2012 18:37:53 GMT

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

Re: Lag function calculation on summrized data?

Mon, 08 Oct 2012 22:23:17 GMT

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

Re: Lag function calculation on summrized data?

swuehl — Mon, 08 Oct 2012 22:36:33 GMT

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