topic Sum Aggr and Count with Multiple If Else Conditions in QlikView

Sum Aggr and Count with Multiple If Else Conditions

danyal1990 — Sat, 16 Nov 2024 00:12:27 GMT

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

Re: Sum Aggr and Count with Multiple If Else Conditions

Kushal_Chawda — Sun, 02 Aug 2020 14:50:13 GMT

How you are getting total visit as 4?

Re: Sum Aggr and Count with Multiple If Else Conditions

danyal1990 — Sun, 02 Aug 2020 15:51:26 GMT

@Kushal_Chawda

For Total Visits:

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

Re: Sum Aggr and Count with Multiple If Else Conditions

Kushal_Chawda — Tue, 04 Aug 2020 16:04:37 GMT

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