Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
=Sum(SalesValue)
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...
something like :
if(Year(salesdate) = 2012,
if(Month(salesdate) = 1,
sum(total if(inmonth(salesdate),makedate(2011,1,1),0) salesvalue)),
if(month(salesdate) = 2,
sum(total if(inmonth(salesdate),makedate(2011,2,1),0) salesvalue)),
......and so on....
A kinda atrocious solution as you can see.
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.
Can anyone offer a better one ?
HI
Try like this
Sum({<Date = {'$(=max(Date))'}>}Sales) for maximum date
Sum({<Date = {'$(=AddYears(max(Date),-1))'}>}Sales) for previous year maximum date
Edit:
To display in same column
=Sum({<Date = {'$(=max(Date))','$(=AddYears(max(Date),-1))'}>}Sales)
Hope it helps
Hi Rafael,
Check the file I attached.
Hope that's what you ment,
Benji
Hi, thanks for your help but all your formulas just give me a column with all 0 values.
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.
This is an example file of what I'm trying to do, in the zip are the qvw and xls files.
The variation expression is exactly what I need, but not in such a way that makes it so hard to understaind/maintain later on...