Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
Try this.
sum({<Year = {"$(=Max(Year)-1"}>}Amount)
Regards,
Kaushik Solanki
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.
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
Thanks, but I was wondering why sum(if(Year='2010',Amount)) shows zero?
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.