AGGR expression excludes part of records

[mkaravan]

Hi, I'm new to QlikView, and dont know how to create KPI comparison with prev month.

Each outlet can achieve some points within month. I have to calculate how many outlets reached 80+ points average in selected month, and previous month, and compare those numbers.

To calculate the KPI of current month, I am using the following expression

COUNT(DISTINCT AGGR(IF( AVG ( [#KPI Execution Fact]) >= 80, [%Outlet ID]),[%Outlet ID]))

and for previous month:

COUNT(AGGR(
IF(AVG({$<[Month] = {"$(=[Month] - 1)"}, [Month Name]=, [%Outlet ID]= > } [#KPI Execution Fact]) >= 80,[%Outlet ID]),[%Outlet ID]))

The problem is, previous month KPI show lesser count of outlets, than it really is. It happens, I believe, because aggr somehow excludes outlets, which were not involved in evaluation in selected month.

To check this, I've made simple pivot table with dimension [%Outlet ID].
when using
IF (AVG({$<[Month] = {"$(=[Month] - 1)"}, [Month Name]=, [%Outlet ID]= > } [#KPI Execution Fact]) >= 80,
[%Outlet ID])

as an expression, count of records in result table is 114 (incorrect)

When replacing [%Outlet ID] with 1 as follows:

IF (AVG({$<[Month] = {"$(=[Month] - 1)"}, [Month Name]=, [%Outlet ID]= > } [#KPI Execution Fact]) >= 80,
1)

count of records become 161 (correct)

When tried just to sum up last expression - received a nested aggregation error, and when using aggr with [%Outlet ID] - it excludes part of outlets, and I'm receiving 114 again

Any help will be appreciated!

[sunny_talwar]

Try this

Count({<[%Outlet ID] = {"=Avg({$<[Month] = {[$(=[Month] - 1)]}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80"}, [Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [%Outlet ID])

or this

Count(Aggr(
If(Avg({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80,
Only({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>}[%Outlet ID])
,[%Outlet ID]))

[mkaravan]

Count({<[%Outlet ID] = {"=Avg({$<[Month] = {[$(=[Month] - 1)]}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80"}, [Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [%Outlet ID])

returns way too much outlets (18088). with distinct - 6693

Count(Aggr(
If(Avg({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80,
Only({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>}[%Outlet ID])
,[%Outlet ID]))

returns zero  sorry, returns  same incorrect 114

[sunny_talwar · Accepted solution]

My bad, try these

Count(DISTINCT {<[%Outlet ID] = {"=Avg({$<[Month] = {[$(=[Month] - 1)]}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80"}, [Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [%Outlet ID])

Added DISTINCT in the above expression

or

Count({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} Aggr(
If(Avg({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80,
Only({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>}[%Outlet ID])
,[%Outlet ID]))

 Added the same set analysis to the outer most count() function as well

[mkaravan]

First expression (I've added closing bracket )

Count(
{$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>}
Aggr(If(Avg({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80,
Only({$<[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>}[%Outlet ID]))
,[%Outlet ID]))

works as expected for 3 of 4 cases (one month still differs, but I suspect some issue with curret month KPI).

second expression works exactly the same way as previous one if [%Outlet ID] is removed from set analysis like so:

Count(DISTINCT {<
[%Outlet ID] = {"=Avg({$<[Month] = {[$(=[Month] - 1)]}, [Month Name], [%Outlet ID]>} [#KPI Execution Fact]) >= 80"},
[Month] = {"$(=[Month] - 1)"}, [Month Name], [%Outlet ID]>} [%Outlet ID])

So, basically, I'm on a right way, thank you  Sunny_talwar so much!

[sunny_talwar]

So it is resolved now?

[mkaravan]

I still need some checks but looks like it is.