Calculating Previous Periods Invoiced Value

[#1]

Hi

I would really appreciate some much needed help and advice on this. I know its basic and I've been reading a lot of discussion threads out there but I am still stuck. I will try and give as much information as I can but I really would be very grateful for some help on this.

Firstly I had to convert my Financial PeriodCode - "PERIODDATE" in my script (loaded from a SQL Server database) because it was a text field showing only 14-01, 14-02 etc. I got QlikView to read it as a date value and to present in the format of "YY-MM" because that's how our financial period works and known as. I used the following to do this:

I used this in my load script:    Date(PERIODCODE, 'YY-MM') as PERIODDATE, after I converted the field in my SQL Select statement: convert(datetime2, '20'+[PERIODCODE]+'-01', 120) PERIODCODE,

So, I have three columns in my straight table, first my dimension (By office) and second and third my expressions:

Expression 1 - Invoiced Current Period:

=SUM({<STATUS={'INVOICED'}>} VALUE)

Expression 2 - Invoice Previous Period:

SUM({<STATUS={'INVOICED'}, PERIODDATE={$(=Only(PERIODDATE)-1)}>} VALUE)

Exp1 works great and gives me back the correct value, but Exp2 only gives me a value of £0.00 everytime, but in the label, it does show that it's reading something but from some research I believe its displaying the year/month numeric value: SUM({STATUS={'INVOICED'}, PERIODDATE={41639}>} VALUE)

When I change the load script to:  num(month(PERIODCODE)) AS PERIODDATE, it works and gives me the previous value but displays the PeriodCode as 1, 2, 3, 4 and so on for each month but with this I have to created a seperate list box so I can define the year and I need to just have one list box displaying each period as 14-07 and so on.

I have seen that people are suggesting to use variables but I have tried this and it hasn't worked for me.

I really hope this makes sense, if you have read this far, thank you so much for taking the time to read this.

Regards

Richard

[jerem1234 · Accepted solution]

So you have:

Date(PERIODCODE, 'YY-MM') as PERIODDATE

Let's say we have the date to be 7/16/2014. It has the equivalent number value of 41836. Even though you change the format to '14-07', the number representation of the date stays the same (41836). Therefore in the set analysis, when you do only(PERIODDate)-1, it will return 41835. But it's not in the same format of YY-MM, so the set analysis won't work.

I'd try this in script:

date(MonthStart(PERIODCODE), 'YY-MM') as PERIODDATE

Then for your one expression, I would use:

sum({<STATUS={'INVOICED'}, PERIODDATE = {"$(=date(addmonths(PERIODDATE, -1), 'YY-MM'))"}>}VALUE)

Here, addmonths will get the date with one month subtracted from the origial date. But then it will return the date in the format of the default (like M/D/YYYY). Then use the date format to make it go back to the YY-MM format.

Please find attached.

Hope this helps!

[#3]

Thank you very much for explaining this to me, it has help tremendously and has worked a treat.

Kind Regards