topic Re: 12 Month Lookback in QlikView

12 Month Lookback

Wed, 23 Jan 2013 14:32:31 GMT

Hi all!

I'm working on a Financial Dashboard for our company that will be providing Profit and Loss statements verses printing out a huge stack of paper P&L statements. One of the requests was to have a 12 month lookback with an P&L statement for example:

P& L Heading	Jan 2013	December 2012	November 2012
LINE HAUL REVENUE	$1234.00	$1222.00	$1423.00
ACCESSORIAL REVENUE	$300.00	$200.00	$600.00
TOTAL REVENUE	$1534.00	$1422.00	$2023

I already have a tab that shows the P&L Headings with the current month but I'm having trouble with the 12 month lookback.

So my dimension is P&L Heading and my first expression is this (which works):

Sum({<PL_LineType={'e'},MTHSTART={'=$(vMaxMonth)'}>} [Amount])+Sum({<PL_LineType={'i'},MTHSTART={'=$(vMaxMonth)'}>} [Amount]*-1)

Where vMaxMonth is =max(MTHSTART). (PL_Linetype is P&L Headings that match 'e' are expenses and 'i' are income lines.)

So how would I go programming the second expression with 1 month less than the selected one?

Thanks in advance!

Re: 12 Month Lookback

Gysbert_Wassenaar — Wed, 23 Jan 2013 14:42:15 GMT

If your month is a numeric field then this should work: MTHSTART={'=$(vMaxMonth)-1'}

If you have a date field you should look into using Qlikview Components. It can create a master calendar for you with a lot of pre-built set expressions that make working with dates and periods a whole lot easier (not to mention less painful). See here for more information.

Re: 12 Month Lookback

Wed, 23 Jan 2013 14:46:07 GMT

I tried it and no luck! I think that vMaxMonth is a date field, this is what is in the variable for me at the moment: 1/1/2013

Re: 12 Month Lookback

Gysbert_Wassenaar — Wed, 23 Jan 2013 15:00:35 GMT

Ah, it's a date. Try MTHSTART={'$(=addmonths($(vMaxMonth),-1))'}

Re: 12 Month Lookback

Wed, 23 Jan 2013 20:12:49 GMT

That's it! I used that along with ignoring some values via set analysis.

Thanks for your help Gysbert!