topic Re: Previous Month Expression in QlikView

Previous Month Expression

Mon, 11 Mar 2013 12:47:27 GMT

Hi,

Please can someone help me with calculating the previous month expression. I have a straight table with number of licenses calculated by:

sum([Total Licenses])

Against this I want to show the same value for the previous month, so I written the below expression:

=sum({$<[Month Key]={$(=[PM Key])}>} [Total Licenses])

[Month Key] is held on every record to show the month e.g. 3_2012.

[PM Key] is also held on every record to show the previous month e.g. 2_2012.

but not working :-<

Grateful for any assitance with this.

Mike.

Re: Previous Month Expression

manideep78 — Mon, 11 Mar 2013 12:51:45 GMT

Sum({<[PM Key]>}[Total Licenses])

Re: Previous Month Expression

Mon, 11 Mar 2013 13:17:53 GMT

Thanks Manideep but this just gives the same total as the current month value.

Re: Previous Month Expression

Mon, 11 Mar 2013 13:50:25 GMT

hope it works

sum({<Month ={$(=Max(Month)-1)}>}[Total Licenses])

Re: Previous Month Expression

Mon, 11 Mar 2013 14:16:26 GMT

Sorry but no joy with that either. Can't do a "month -1" because my month is a string. Even with integers this would not deal with January correctly.

Re: Previous Month Expression

manideep78 — Mon, 11 Mar 2013 14:20:17 GMT

Try this

Sum({<[Month Key]=[PM Key]>}[Total Licenses])

Hope it helps

Re: Previous Month Expression

Mon, 11 Mar 2013 14:23:35 GMT

Thanks but still no joy. I think that expression would look for [Total Licenses] where [Month Key] = "PM Key"... this will never be true. I think it needs to be where the value of [PM Key] = [Month Key].

Re: Previous Month Expression

manideep78 — Mon, 11 Mar 2013 14:38:35 GMT

What does your [Month Key] represents? what is the actual data int that field? Does your [PM Key] represents only your previous month data???

Re: Previous Month Expression

Mon, 11 Mar 2013 14:45:23 GMT

Let me give an example:

[Month Key] [Total Licenses] [PM Key]

12_2012 95 11_2012

1_2013 100 12_2012

2_2013 105 1_2013

3_2013 120 2_2013

.. this should then be displayed in a straight table as...

Month [Total Licenses] [Last Month]

Dec-2012 95 -

Jan-2013 100 95

Feb-2013 105 100

Mar-2013 120 105

Re: Previous Month Expression

Mon, 11 Mar 2013 14:55:17 GMT

Hi Michael,

try the above() function, eg above([Total Licences]).

Re: Previous Month Expression

Mon, 11 Mar 2013 14:57:38 GMT

Have already tried that - Above only works when the month stay in the correct sort order, and it also goes wrong when the user makes a select that excludes previous month.

Re: Previous Month Expression

Mon, 11 Mar 2013 15:16:07 GMT

The sort order thing is true - are you having problems with that? There are ways around sort problems. However, I have a previous month expression in my code and it continues to function even when the month is restricted. What code are you using?

Re: Previous Month Expression

Mon, 11 Mar 2013 16:26:46 GMT

I am displaying my table horizontally so don't think the Above() function will work for me.

Re: Previous Month Expression

Mon, 11 Mar 2013 16:42:09 GMT

In that case Before() will serve a similar purpose.

Re: Previous Month Expression

Mon, 11 Mar 2013 17:12:23 GMT

Sorry, I am acutally using a pivot table rather than a straight table so Above() or Below() will not work.