Count of rows using aggr

[#1]

Hi,

I have a straight table as below.

Retail(Managers)	KPI1	KPI2	KPI1/KPI2	Grade
Anu	100	200	0.5	B
Manka	300	430	0.69	A
Tunai	100	150	0.66	A
Ruku	200	450	0.44	C
Payel	150	300	0.5	B

The First column is a cyclic group named Retail. The cyclic group is defined as follows:

Region---->Zone--->Managers--->Field Employee---->Store.

Store is the lowest level in the hierarchy of cyclic group. The logic for the above table is simple. If KPI1/KPI2 is equal to 0.5 then Grade= B, if Greater then A else C.

I have pre-selected the Zone named as US East in the list box.

I need to display a table as below

Retail(Zone)	Number of A	Number of B	Number of C
US East	2	2	1

So,all the above Managers belonged to the Zone named as US East. When I cycle up in the Cyclic group to show Zone, I should see the count of Managers under that Zone having A, B or C. Similarly for other Zones as well.

I have used this expression with no luck:

If

(

getcurrentfield([Retail])='Zone' ,

count

  (

  aggr

  (

  If

  (KPI1/KPI2)=-0.5,1),MANAGER_NAME

  )

  ) ,

If

(

getcurrentfield([Retail])='Managers' ,

count

  (

  aggr

  (

  If

  (KPI1/KPI2)=-0.5,1),FIELD_EMPLOYEE_NAME

  )

  ))

Appreciate your help to determine the issue and help with a working solution to achieve the result.

Thanks,

Tutan

[Nicole-Smith]

Please do not post the same question multiple times.  The original thread is being replied to here: Expression help

[#3]

Hey Tutan,

This is just a suggestion. Dunno if it will be applicable for your case.

Why don't you create a flag in the backend like Grade using the same logic you are using on the front end. Once you have the flag all you have to do is use an If condition and count the flag for those three expressions in the second table.

Please let me know if it was helpful.

Thanks,

AJ

[#4]

Hi AJ,

I like the idea. But, I want to know where is the issue here. Just wanted to save some time in writing a new piece of code. I have a feeling that selection in the cyclic group is a problem here. I have used a similar kind of an expression by omitting the cyclic group selection part, just for counting number of store and it just worked fine across hierarchies.Just curious to know where am I doing it incorrectly...

Regards,

Tutan

[#5]

Sorry Nicole, my apologies...

[Nicole-Smith]

No problem, Tutan.  Questions are easier to answer when having the entirety of the information, which someone would be missing had they not read the other thread.  It just makes it easier if everything is in one place

[#7]

The reason I suggested back end calculation is because the If condition on front end is resource intensive so it could have some impact on performance.

But if you wanna use it on Front end, please try this one:

=if(GetCurrentField([Retail])='Zone', count(if(aggr(((KPI1)/(KPI2)),Managers)>.5,1)))

I just moved If and aggr around and this is only for A. You can do the same for the remaining two.

I have attached a sample file. Also please note that the second table will work only when the Group is 'Zone'.

Thanks

AJ