4 Replies Latest reply: Jan 23, 2013 3:12 PM by Andrew Landry

12 Month Lookback

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:

Jan 2013December 2012November 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?

• Re: 12 Month Lookback

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

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

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

• Re: 12 Month Lookback

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