topic Re: Set Analysis - calculate data from the past in QlikView

Set Analysis - calculate data from the past

Wed, 24 May 2017 12:59:06 GMT

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

Re: Set Analysis - calculate data from the past

MK_QSL — Wed, 24 May 2017 13:06:21 GMT

Try

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

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

Re: Set Analysis - calculate data from the past

Anil_Babu_Samineni — Wed, 24 May 2017 13:06:43 GMT

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

antoniotiman — Wed, 24 May 2017 13:13:49 GMT

Try

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

Regards,

Antonio

Re: Set Analysis - calculate data from the past

Wed, 24 May 2017 13:18:36 GMT

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

Wed, 24 May 2017 13:19:12 GMT

there is nothing selected, all selections are cleared

Re: Set Analysis - calculate data from the past

MK_QSL — Wed, 24 May 2017 13:58:36 GMT

What exactly you are looking for?

1) If something selected

2) If nothing selected..

Re: Set Analysis - calculate data from the past

Wed, 24 May 2017 14:05:18 GMT

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

Re: Set Analysis - calculate data from the past

MK_QSL — Wed, 24 May 2017 14:58:50 GMT

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

sonysree88 — Fri, 26 May 2017 10:37:32 GMT

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

sonysree88 — Fri, 26 May 2017 10:38:51 GMT

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