topic Re: Comparing values between years in QlikView

Comparing values between years

Anonymous — Thu, 07 May 2015 21:52:57 GMT

Hi,

I have a table with this data:

year - product - sales

I want to make an expersion to show the sales of previous year. I have year 2014 and 2013 in the same table in the field.

I want to know how can I show the data of sales of previous year in order to compare with data of this year.

I don't want to create a new table for 2013. I suppose is possible to do with all the data in the same table.

year product sales sales previous year

2014 P1 485 500

Is like an above function but moving in one dimension.

Can anybody help me?

Re: Comparing values between years

petter — Thu, 07 May 2015 22:10:22 GMT

The simplest solution is like this:

Drop the dimension called year and make two expressions like this:

Sum( { <Year={2015}> } Sales )

and

Sum( { <Year={2014}> } Sales )

You can make them dynamic by using variables like this:

In your load script:

SET Year0 = =Max(Year);

SET Year1 = =Max(Year)-1;

The expressions should then be:

Sum( { <Year={$(Year0)}> } Sales )

and

Sum( { <Year={$(Year1)}> } Sales )

Re: Comparing values between years

Anonymous — Thu, 07 May 2015 22:27:51 GMT

Hi Petter,

It doesn't work....

It show the same in year and previous year sales.

year is a dimension in my chart, but I don't know how to refer to the value of the dim in the expresion

I want to do something like this:

sum( <year={actual value of the dim year - 1} sales)

Why do you use max()?

Thanks in advance,

Re: Comparing values between years

petter — Thu, 07 May 2015 22:29:53 GMT

I modified my first post ... because I was a bit too quick ... did you look at my modified suggestion? I actually verified in a small app that it worked ....

Re: Comparing values between years

ramoncova06 — Thu, 07 May 2015 22:35:34 GMT

this should give you the previous year sales

sum({< anio= {"$(=(max(anio,2))}>}ventas)

the reason why max is used is because the assumption that the greatest year is equal to the current year

Re: Comparing values between years

Anonymous — Thu, 07 May 2015 22:40:55 GMT

Ok, thanks Petter, it works properly.

Is there any other way using the dim year and refering always to (year-1).

I don't know how many years can have my table.

Thanks in advance

Re: Comparing values between years

petter — Thu, 07 May 2015 22:50:23 GMT

Then you drop the Set Expression and have your two dimensions: Product and Year

Have the simple expression Sum( Sales )

And make a pivot chart ... drag the year up to the upper/top right and then you get all the years as columns...