topic Re: Consider a particular State with AGGR function in QlikView

Consider a particular State with AGGR function

Anonymous — Wed, 01 Mar 2017 14:26:03 GMT

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.

Re: Consider a particular State with AGGR function

sunny_talwar — Wed, 01 Mar 2017 15:51:18 GMT

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

Re: Consider a particular State with AGGR function

Anonymous — Thu, 02 Mar 2017 04:17:06 GMT

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.