topic Re: Question on Aggr and set analysis in QlikView

Question on Aggr and set analysis

gerrycastellino — Wed, 25 Nov 2020 16:16:04 GMT

I have a chart where I need to aggregate by Year, Month, Country all managers with <= 2 direct reports.

When I select an individual month I get the right results in my count (264).

When I select a range of months, I get an incorrect result:

My Expression is:

Count(

Aggr(

Sum({<SL_SPRVSRY_ORG_WID={"=Aggr(Sum(TOTAL_ROLL_UP_COUNT),SL_SPRVSRY_ORG_WID)<=2"}>} TOTAL_ROLL_UP_COUNT)

,SL_SPRVSRY_ORG_WID

)

My dimensions are Year, Month, country.

SL_SPRVSRY_ORG_WID is a manager ID.

Thankx.

Gerry.

vishsaggi — Fri, 06 Jul 2018 19:49:31 GMT

Can you share a sample to work on? may be try like

= Count(

Aggr(

Sum({<SL_SPRVSRY_ORG_WID={"=Aggr(Sum(TOTAL_ROLL_UP_COUNT),SL_SPRVSRY_ORG_WID)<=2"}>} TOTAL_ROLL_UP_COUNT)

,SL_SPRVSRY_ORG_WID, [Fiscal Month]

)

sunny_talwar — Fri, 06 Jul 2018 19:50:14 GMT

May be this

Count(Aggr(If(Sum(TOTAL_ROLL_UP_COUNT) <= 2, 1, 0), SL_SPRVSRY_ORG_WID, Year, Month, Country))

gerrycastellino — Fri, 06 Jul 2018 21:23:17 GMT

Sunny:

Your solution worked well, with a little tweak:

I've put the DR in a variable, which makes it more flexbile in the expr.

I'm having a small problem in my pivot table, the TOTAL numbers are accurate, but not displaying in line with the countries, as below:

sunny_talwar — Sat, 07 Jul 2018 02:17:34 GMT

Not entirely sure, but may be you need to add Country to the Aggr() function....