Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danyal1990
Contributor II
Contributor II

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

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:

OSA Data.jpg

Labels (3)
1 Solution

Accepted Solutions
Kushal_Chawda

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))

View solution in original post

5 Replies
sunny_talwar

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))
danyal1990
Contributor II
Contributor II
Author

@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?

 

sunny_talwar

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))
danyal1990
Contributor II
Contributor II
Author

@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:

Changed Data.jpg

 

Kushal_Chawda

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))