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:
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.
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
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.
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
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.
