topic Re: Re: Nested aggregation/set analysis difficulty... in QlikView

Nested aggregation/set analysis difficulty...

Sat, 21 Dec 2013 13:28:27 GMT

Hi friends,

I have 2 tables:

Table A: and Table B:

Table A is linked to Table B through 'Material'.

I have to calculate the individual rating of vendors in Table A:

For individual Vendor, Rating = Count( if (RATE<=Min(OLD_RATE)),Material) / Count (Material) * 100

Since the formula requires nested aggregation, the pivot table throws the error.

I have to calculate this rating for all the vendors only in front end (no reload is possible), as the entries in above 2 tables are selected through "date" filter entered by the user. (No input for vendor or material is made).

I tried my had at set analysis but could not find a solution.

Please provide the solution.

Re: Nested aggregation/set analysis difficulty...

Gysbert_Wassenaar — Sat, 21 Dec 2013 15:16:01 GMT

Maybe this:

Count( if (RATE<= aggr(Min(OLD_RATE),Material)),Material) / Count (Material) * 100

Re: Re: Nested aggregation/set analysis difficulty...

Mon, 23 Dec 2013 07:52:34 GMT

Thank you so much. The formula works. However I am facing a new problem. When I apply the formula in the pivot table:

Count( if (RATE<= aggr(Min(OLD_RATE),Material),Material)) / Count (Material) * 100

the highlighted portion of the formula returns a single value (that is minimum value) of price for the material from table B (which is correct).

However when that same material is supplied by more than 1 vendor, that is entry for that material is coming twice in table A, then the minimum value is displayed/considered only once and for every further occurrence for that material a null is displayed/returned. In such cases the formula does not work.

Please guide me to solve this issue...

Re: Re: Re: Nested aggregation/set analysis difficulty...

tresB — Mon, 23 Dec 2013 08:30:00 GMT

Try with NODISTINCT keyword, like:

Count( if (RATE<= aggr( NODISTINCT Min(OLD_RATE),Material),Material)) / Count (Material) * 100

Re: Nested aggregation/set analysis difficulty...

Mon, 23 Dec 2013 08:47:24 GMT

Thank You!!!

I absolutely love this community. For newbies in Qlikview like me, it is the ultimate source...

You guys Rock.