Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted
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]

                )

              )

Highlighted

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

Creator III
Creator III

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

Highlighted

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