Set Analysis - calculate data from the past

[#1]

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

=Sum({<Year_Month={"=$(=MonthName(AddMonths(Year_Month,-3)))"}>}FC_KG)

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

Thanks for you answer

Kind regards

[MK_QSL]

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)

[Anil_Babu_Samineni]

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

[antoniotiman]

Try

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

Regards,

Antonio

[#5]

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

[#6]

there is nothing selected, all selections are cleared

[MK_QSL]

What exactly you are looking for?

1) If something selected

2) If nothing selected..

[#8]

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

thanks for your reply

[MK_QSL]

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)

[sonysree88]

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