12 Month Lookback

[#1]

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!

[Gysbert_Wassenaar]

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.

[#3]

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

[Gysbert_Wassenaar · Accepted solution]

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

[#5]

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

Thanks for your help Gysbert!