topic Re: Nested Sum(Aggr(Sum())) and match with Criteria in QlikView

Nested Sum(Aggr(Sum())) and match with Criteria

danyal1990 — Sat, 16 Nov 2024 18:15:17 GMT

Hi All,

I have a scenario of calculating a Visit as Compliant based on the Categories (if matching criteria). But i am unable to do a nested sum or overall count on below expression. Can you please guide and help me out.

I will be very thankful to you.

CRITERIA:

Category-6 : 14

Category-13: 10

TRIED EXPRESSION:

if(
sum(aggr(sum({<Parameter={'OSA-1','OSA-2','OSA-3'},Store_Type={'Cosmetics'},Category={'Category-6'}>} Achieved), Storecode,Visit,Category))
>=14,1,0)
+
if(
sum(aggr(sum({<Parameter={'OSA-1','OSA-2','OSA-3'},Store_Type={'Cosmetics'},Category={'Category-13'}>} Achieved), Storecode,Visit,Category))
>=10,1,0)

DATA and RESULT I AM GETTING vs EXPECTED:

Re: Nested Sum(Aggr(Sum())) and match with Criteria

sunny_talwar — Fri, 07 Aug 2020 10:55:54 GMT

May be try this

Sum(Aggr( If( Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type = {'Cosmetics'}, Category = {'Category-6'}>} Achieved) >= 14 or Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type ={'Cosmetics'}, Category = {'Category-13'}>} Achieved) , Visit) , Visit, Category))

Re: Nested Sum(Aggr(Sum())) and match with Criteria

danyal1990 — Fri, 07 Aug 2020 12:55:05 GMT

@sunny_talwar Really appreciate your effort.

i tried your provided solution but it is not returning any result.

Can you please suggest some more option?

Re: Nested Sum(Aggr(Sum())) and match with Criteria

sunny_talwar — Fri, 07 Aug 2020 15:29:29 GMT

Sorry my bad, can you try this

Count(DISTINCT Aggr( If( Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type = {'Cosmetics'}, Category = {'Category-6'}>} Achieved) >= 14 or Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type ={'Cosmetics'}, Category = {'Category-13'}>} Achieved) , Visit) , Visit, Category))

Re: Nested Sum(Aggr(Sum())) and match with Criteria

danyal1990 — Fri, 07 Aug 2020 19:37:24 GMT

@sunny_talwar

I tried your updated expression and it is producing near perfect results. Can you please help one more time?

The problem is that it is not checking if both the categories are compliant for the visit.

After changing the Data for one Category of Visit-1 but the expression still counting that Visit as Compliant.

It should be excluded.

MODIFIED EXPRESSION:

=Count(DISTINCT
Aggr(
If(
Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type = {'Cosmetics'},Category = {'Category-6'}>} Achieved) >= 14
or
Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type ={'Cosmetics'}, Category = {'Category-13'}>} Achieved) >= 10
,Visit)
,Visit, Category)
)

Changed DATA still counting Visit-1 as Compliant based on only 1 Category while it should check both Categories:

Re: Nested Sum(Aggr(Sum())) and match with Criteria

Kushal_Chawda — Fri, 07 Aug 2020 21:15:02 GMT

try below

count(distinct aggr(if(sum(total<Store_Type,Category>aggr(if(Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type = {'Cosmetics'},Category = {'Category-6'}>} Achieved) >=14,1),Category,Store_Type))=1,Visit),Category,Store_Type,Parameter)) + count(distinct aggr(if(sum(total<Store_Type,Category>aggr(if(Sum({<Parameter = {'OSA-1','OSA-2','OSA-3'}, Store_Type ={'Cosmetics'}, Category = {'Category-13'}>} Achieved) >=10,1),Category,Store_Type))=1,Visit),Category,Store_Type,Parameter))