10 Replies Latest reply: May 26, 2017 6:38 AM by sonygot g

# Set Analysis  - calculate data from the past

Hello,

I want to calculate data from a moment in the past with a formula and set analysis, but it does not work like this

Year_Month  has format 'mon YY', such as Apr 2017

Thanks for you answer

Kind regards

• ###### Re: Set Analysis  - calculate data from the past

Try

=SUM({<Year_Month = {"\$(=Date(AddMonths(Max(Year_Month),-3),'MMM YY'))"}>}FC_KG)

or

=SUM({<Year_Month = {"\$(=Date(AddMonths(Max(Year_Month),-3),'MMM YYYY'))"}>}FC_KG)

• ###### Re: Set Analysis  - calculate data from the past

this is working but only shows data for 1 month, if I remove the Max function, it is no longer working, no translation is done in the formula

• ###### Re: Set Analysis  - calculate data from the past

What exactly you are looking for?

1) If something selected

2) If nothing selected..

• ###### Re: Set Analysis  - calculate data from the past

hi Manish,

I have a table with year_month, material and data,

I want to show the data from the past, meaning the data that was reported 3 months ago,

so, if in the table year_month is equal to apr 2017, it should show the data of jan 2017, and this for all months in the table,

I cannot get this working, have tried a number of different syntaxes

• ###### Re: Set Analysis  - calculate data from the past

You can't use Set Analysis in this case.. As set expression is calculated once per chart..
you can use something like

Above(Sum(Sales),3)

• ###### Re: Set Analysis  - calculate data from the past

IS there any selections happening in Year_Month ??? If so, Remove equal symbol before the \$ indicator and then check

• ###### Re: Set Analysis  - calculate data from the past

there is nothing selected, all selections are cleared

• ###### Re: Set Analysis  - calculate data from the past

Try

Sum({<Year_Month={'\$(=MonthName(Date#(Year_Month,'MMM YYYY'),-3))'}>} FC_KG)

Regards,

Antonio

• ###### Re: Set Analysis  - calculate data from the past

Please use the below expression in the expression tab

sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={'>=\$(=date(date#('01/09/2016','DD/MM/YYYY'),'MMM-YY'))<=\$(=max(FinResultsDate))'}>} [USD amount])

Regards,

Sony

• ###### Re: Set Analysis  - calculate data from the past

Sorry above calculation is for current date selections ,

Please use this :

=SUM({<Year_Month = {"\$(=Date(AddMonths(Max(Year_Month),-3),'MMM YYYY'))"}>}FC_KG)

Regards,

Sunitha