Consider a particular State with AGGR function

[Anonymous]

Hi Team,

I am trying to do Comparative Analysis in Qlikview11. I have defined 2 States as StateA and StateB and also have the default State.

I have an Expression which reads something like 

(sum({StateA} SalesValue)

/

  aggr(nodistinct sum({StateA <YearMonth={$(vYearMonthList)}, GlobalProduct=,PPG=,InternalProdct=,BrandEquity=,Manufacturer=,SubBrand=,Variant=,%IntProd=>} SalesValue),  ProductSubCategory,Customer) )*100

My Question is "How can I write to ignore all Selections present for default State. Presently, Imagine I Select Customer1 from default State ListBox and Customer2 from StateA then I get no Results. Only if I manually clear Customer1 from default then I see data coming for the above expression. Can you please tell me EXACT syntax so that only StateA would be considered in my calculations. I guess if it was not for AGGR, it would have worked correctly. Kindly help.

Thanks.

Kuldeep.

[sunny_talwar · Accepted solution]

Try this:

(Sum({StateA} SalesValue)

/

Only({StateA <YearMonth={$(vYearMonthList)}, GlobalProduct=, PPG=, InternalProdct=, BrandEquity=, Manufacturer=, SubBrand=, Variant=, %IntProd=>} Aggr(NODISTINCT Sum({StateA <YearMonth={$(vYearMonthList)}, GlobalProduct=, PPG=, InternalProdct=, BrandEquity=, Manufacturer=, SubBrand=, Variant=, %IntProd=>} SalesValue),  ProductSubCategory,Customer))) * 100

[Anonymous]

Thanks Sunny for the help. I was able to get Desired results since now it does not consider other Selections and looks only for StateA. I have shared the output to my User. Will write back if I face any challenge on this question.

Thanks.

Kuldeep.