Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've seen several threads about this subject, but I can't meake this to work in my App.
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.
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
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
SUM({<Year = {'$(=AddYears(Max(Year),-1))'}>}Sales)
Can you tell me are you creating Year field using derived field ?
Thanks for your quick response!.
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.
For Current Year
=SUM({<Year = {'$(=Max(Year))'}>}Sales)
For Previous Year
=SUM({<Year = {'$(=Max(Year)-1)'}>}Sales)
Can u attach sample of your data, it will be helpful.
I've attached the App.
Hope this help clarify.
Many Thanks
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
Hi Rod,
Seems to work!.
Thank you very much to everyone.