Sum Aggr and Count with Multiple If Else Conditions

[danyal1990]

Good Day All,

I am working on store related data and i have to calculate compliance on each visit for a KPI but the problem is we have different store types with different criteria applied on it.

I have write below expression but there is some mistake in it, can you please help me out where i am making the mistake.

Expression:

sum(aggr(
If(count
(distinct
if
(Parameter='KPI-1' and Compliance='C' and Store_Type<>'Cosmetics',Category)
)>=5,1,if(Parameter='KPI-1' and Compliance='C' and Store_Type='Cosmetics',Category)>=2,1)
,Visit,Compliance))

Criteria:

Grocers =5, Cosmetics=2

[Kushal_Chawda]

How you are getting total visit as 4?

[danyal1990]

@Kushal_Chawda 

For Total Visits:

=Sum( Aggr(Count(DISTINCT if(Parameter='KPI-1', Visit)), Storecode))

[Kushal_Chawda · Accepted solution]

You can try below

sum(aggr(if(Count({<Parameter={'KPI-1'},Compliance={'C'},Store_Type={'Grocers'}>}Visit)>=5,1),Store_Type,Compliance,Visit))+
sum(aggr(if(Count({<Parameter={'KPI-1'},Compliance={'C'},Store_Type={'Cosmetics'}>}Visit)>=2,1),Store_Type,Compliance,Visit))