Analyzing Current and Previous Year data (See description)

[Anonymous]

I have the data set where it has Year as a dimension. It has data for the last 10 years. Say from 2005 to 2015.

My client always wants to analyse the current year's data and previous year data.

Say, I have a Bar Chart which shows the Sales of each Product for 2014 and 2015. For Time being I just used a simple Calculated dimension =If(Year = 2014 or Year=2015,Year) so that other data will be suppressed as NULL.

Not just the above scenario. I have used {<Year=2015>} in various expression which I need to convert to current available Year data.

The least, I could think of is max(Year) and max(Year)-1. Is there any alternatives available for this?

Can some one throw a light on this about how to proceed with this problem? so that when I do incremental load, the current data and previous year will be taken.

[ecolomer]

Use a variable with value = Max(Year)

[Anonymous]

Hi. Yes. I thought about that and also added that to my question. But is there any other way? Like using variables?

[Anonymous]

Also. When I use max(Year) inside set, there is some error in my set expression. How to overcome that?

[ecolomer]

Can you upload file qvw or an example with your data?

[#6]

Hello,

You may do this with Set Analysis and variables. You need:

- Variable var_CurrentYear = GetFieldSelections(Year,True())

- Variable var_LastYear = GetFieldSelections(Year,True())-1

- Expression for this year (Example): =SUM({$<Año={'$(var_CurrentYear)'}>}[Sales])

- Expression for previous year (Example): SUM({$<Año={'$(var_LastYear)'}>}[Sales])

- Set the Calculation Condition of the objects using this Set Analysis to 'if(GetSelectedCount(Year,True())=1,1,0)' so it won't 'crash' when more than one year is selected.