topic Re: Count of rows using aggr in QlikView

Count of rows using aggr

Tue, 07 Jan 2014 18:22:12 GMT

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:

(

getcurrentfield([Retail])='Zone' ,

count

(

aggr

(

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

)

) ,

(

getcurrentfield([Retail])='Managers' ,

count

(

aggr

(

(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

Re: Count of rows using aggr

Nicole-Smith — Tue, 07 Jan 2014 18:32:00 GMT

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

Re: Count of rows using aggr

Tue, 07 Jan 2014 18:41:49 GMT

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,

Re: Count of rows using aggr

Tue, 07 Jan 2014 18:51:19 GMT

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

Re: Count of rows using aggr

Tue, 07 Jan 2014 18:51:46 GMT

Sorry Nicole, my apologies...

Re: Count of rows using aggr

Nicole-Smith — Tue, 07 Jan 2014 18:53:22 GMT

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

Re: Re: Count of rows using aggr

Tue, 07 Jan 2014 19:41:15 GMT

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