topic Re: Set analysis inside set analysis review formula in QlikView

Set analysis inside set analysis review formula

haribabugv — Sat, 16 Nov 2024 21:50:01 GMT

Hello Qlikusers,

Need your help to update this formula to have set analysis inside set analysis.

I need to calculate forecast accuracy for definite period using vStart and vEnd for each planner.

So that chart should have three lines and we can compare forecast accuracy of planner A, Planner B and Overall.

With the below code, i can calculate forecast accuracy. I need to create similar expression for each planner (ie A and B) . So can anyone give me leads on how to include field 'Planner' in the below set analysis expression?

num(sum({<Date*={">$(vStart)<$(vEnd)"}>}Sales * if((1-(fabs(Sales-Forecast))/Sales)<0,0,(1-(fabs(Sales-Forecast))/Sales)))/sum({<Date*={">$(vStart)<$(vEnd)"}>} Sales),'##0%')

@sunny_talwar

Re: Set analysis inside set analysis review formula

agni_gold — Mon, 26 Nov 2018 04:39:48 GMT

Check attached one

Re: Set analysis inside set analysis review formula

jonathandienst — Mon, 26 Nov 2018 06:25:53 GMT

Probably something like:

sum({<Date *= {">$(vStart)<$(vEnd)"}, Planner = {'Planner A'}>} Sales *
If((1-(fabs(Sales-Forecast)) / Sales) < 0, 0,
(1-(fabs(Sales-Forecast))/Sales))
)
/ sum({<Date *= {">$(vStart)<$(vEnd)"}, Planner = {'Planner A'}>} Sales)

Similarly for Planner B, C.