Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Ah, it's a date. Try MTHSTART={'$(=addmonths($(vMaxMonth),-1))'}
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.
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
Ah, it's a date. Try MTHSTART={'$(=addmonths($(vMaxMonth),-1))'}
That's it! I used that along with ignoring some values via set analysis.
Thanks for your help Gysbert!