Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 :

=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 ?

4 Replies
MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Hi Rafael,

Check the file I attached.

Hope that's what you ment,

Benji

Not applicable
Author

@Mayil Vahanan Ramasamy

Hi, thanks for your help but all your formulas just give me a column with all 0 values.

@benjiazaria

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.

Not applicable
Author

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