Set analysis inside set analysis review formula

[haribabugv]

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

[agni_gold · Accepted solution]

Check attached one

[jonathandienst]

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.