Nested aggregation/set analysis difficulty...

[#1]

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.

[Gysbert_Wassenaar]

Maybe this:

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

[#3]

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

[tresesco · Accepted solution]

Try with NODISTINCT keyword, like:

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

[#5]

Thank You!!!

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

You guys Rock.