Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try with NODISTINCT keyword, like:
Count( if (RATE<= aggr( NODISTINCT Min(OLD_RATE),Material),Material)) / Count (Material) * 100
Maybe this:
Count( if (RATE<= aggr(Min(OLD_RATE),Material)),Material) / Count (Material) * 100
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...
Try with NODISTINCT keyword, like:
Count( if (RATE<= aggr( NODISTINCT Min(OLD_RATE),Material),Material)) / Count (Material) * 100
Thank You!!!
I absolutely love this community. For newbies in Qlikview like me, it is the ultimate source...
You guys Rock.