Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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:

P& L Heading
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?

Thanks in advance!

1 Solution

Accepted Solutions

Re: 12 Month Lookback

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


talk is cheap, supply exceeds demand
4 Replies

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.


talk is cheap, supply exceeds demand
Not applicable

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))'}


talk is cheap, supply exceeds demand
Not applicable

Re: 12 Month Lookback

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

Thanks for your help Gysbert!

Community Browser