Question on getting previous year values using date as dimension...
So, if I have a salesdate date Field and a salesvalue field and need to compare current values against the previous year values, how do I do this ?
I created a straight table chart and used for dimension : MonthName(salesdate).
Then I created the expression Sales Value with :
That correctly gives me the sales value for the correct month-year dimension.
Now I need to create a variation expression that is the sales for the current month / sales of the previous year month. (for example, january 2012 / january 2011)
I have found dozens of examples but none of them got anywhere near to working, which leads me to believe that those examples don't have date as a dimension of the chart.
The only way I could get it to work was by making a if statement outside the sum, considering every single possibility of month-year combination, but I can't believe there isn't a more intelligent way...
I can't dynamically set the date into the makedate function because (I believe) of TOTAL, but if I remove the total then it will not have the previous year values to work with because the monthname(salesdate) dimension in the chart will filter them out.
Thanks for your help, it's a nice suggestion, but we still have to manually create every year as expression and if the user wants see the current year and only the variation from previous year there's no way I can dynamically tell which year he's looking at.
I will try to make a better example and will post soon. I can't believe qlikview doesn't have an easier/better way to work with periods.