topic How to calculate the previous year data in QlikView

How to calculate the previous year data

Mon, 27 Jun 2011 07:38:06 GMT

Hi All,

I have the following table:

load * Inline
[
Year,Month,Amount,Region
2010,1,3000,Brazil
2011,1,5000,Brazil

]

Now I want to get the previous year data based on the current year, I use pivot table to achieve;

The Dimension is Region, and I put two Measures into the pivot: sum(if(Year='2011',Amount)) , sum(if(Year='2010',Amount)).

And I also put Year as a parameter, when I select 2011, the 2010's data is zero, so how to calculate the previous year data?

Thanks.

How to calculate the previous year data

kaushiknsolanki — Mon, 27 Jun 2011 08:03:11 GMT

Hi,

Try this.

sum({<Year = {"$(=Max(Year)-1"}>}Amount)

Regards,

Kaushik Solanki

How to calculate the previous year data

Mon, 27 Jun 2011 08:27:32 GMT

Hi,

Now I have another question, based on my understanding, the selection should filter the data, for example: if I choose 2011 on Year, the dataset just includes 2011's data not 2010. So the expression sum(if(Year='2010',Amount)) always shows zero, but why sum({<Year = {"$(=Max(Year)-1"}>}Amount) does not show zero?

Thanks.

How to calculate the previous year data

kaushiknsolanki — Mon, 27 Jun 2011 08:33:00 GMT

Hi,

The expression i gave you will give you previous years data.

Means if you select the 2011, it will give value for 2010.

Regards,

Kaushik Solanki

How to calculate the previous year data

Mon, 27 Jun 2011 08:45:19 GMT

Thanks, but I was wondering why sum(if(Year='2010',Amount)) shows zero?

How to calculate the previous year data

Mon, 27 Jun 2011 08:49:33 GMT

The formula: sum({<Year = {"$(=Max(Year)-1"}>}Amount) uses a set expression. In the QV manual there is a part about set analysis en set expressions, I advise you to read that part.