8 Replies Latest reply: Apr 13, 2015 6:08 AM by Juan Carlos Vera Hernandez

# Year comparison

Hi,

The App is showing hierarchical data by (year, month,  Area, Sub-Area, Provider, Location).

I just want to show a line-chart comparing the dimensions selected against previous period (year). And what I did is create a new measure (Previous Year Sales) as:

sum( {\$<Year = {\$(<Year-1)>}>} Sales )

But I get the same amount than the current year. I've tried with different syntax unsuccessfully.

Where is the problem here?

Thanks a lot.

• ###### Re: Year comparison

Current Year

SUM({<Year = \$(=Year(Today()))>}Sales)

Previous Year

SUM({<Year = {'\$(=Year(Today())-1)'}>}Sales)

If you want Current and Previous Year based on User Selected Years... use Max(Year) instead of Today()

Current Year

SUM({<Year = \$(=Year(Max(Year)))>}Sales)

Previous Year

• ###### Re: Year comparison

Can you tell me are you creating Year field using derived field ?

• ###### Re: Year comparison

Manish: I tried SUM({<Year = {'\$(=AddYears(Max(Year),-1))'}>}Sales) and i'm getting zero as Sales.

Current Year it's not neccesary cause this information is in the table. I just want to calculate Sales based on current selection and Year -1.

Ashish: I don't understant what you mean derived field. I've a table  (year, month,  Area, Sub-Area, Provider, Location, Sales). I've defined a mesure for Previous Year Sales, and want to compare Sales with PY Sales, according to user selecction, ex: if user selects 2010, need to show 2010 Sales and 2009 Sales. I understand that 2010 is directly provided by table data and PY sales must be calculated according to user selection.

• ###### Re: Year comparison

For Current Year

=SUM({<Year = {'\$(=Max(Year))'}>}Sales)

For Previous Year

=SUM({<Year = {'\$(=Max(Year)-1)'}>}Sales)

• ###### Re: Year comparison

I've attached the App.

Hope this help clarify.

Many Thanks

• ###### Re: Year comparison

Hi Juan,

If you use the logic presented by other responses and then take out the Year from your pivot table you will get something that will work as expected.

I have attached a really simple model that hopefully illustrates what you want.

Hope this helps.

Kind regards,

Rod

• ###### Re: Year comparison

Hi Rod,

Seems to work!.

Thank you very much to everyone.