Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gerrycastellino
Creator III
Creator III

Question on Aggr and set analysis

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

Aggr1.PNG

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

Aggr2.PNG

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.

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

4 Replies
vishsaggi
Champion III
Champion III

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

May be this

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

gerrycastellino
Creator III
Creator III
Author

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.

Aggr1.PNG

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:

Aggr2.PNG

sunny_talwar

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