Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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))
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))
@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?
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))
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:
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))