Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: Question on getting previous year values using date as dimension...

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

Not applicable

Re: Question on getting previous year values using date as dimension...

Hi Rafael,

Check the file I attached.

Hope that's what you ment,

Benji

Not applicable

Re: Question on getting previous year values using date as dimension...

@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

Re: Question on getting previous year values using date as dimension...

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

Community Browser