How to calculate the previous year data

[#1]

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.

[kaushiknsolanki]

Hi,

    Try this.

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

Regards,

Kaushik Solanki

[#3]

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.

[kaushiknsolanki · Accepted solution]

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   

[#5]

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

[#6]

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.