topic Re: Aggregating two dimension values with same Rank in QlikView

Aggregating two dimension values with same Rank

mrthomasshelby — Fri, 29 Jun 2018 09:03:15 GMT

Hello,

I have a table that ranks vendors based on a measure 'Variance to Estimate' as in the image below:

Everything works fine but say if two Vendors quote the same price and their variance to estimate is the same, the values get aggregated and the Vendor names also don't get populated. For example in the table above, for Package Labour Category 1, Vendor O and Vendor J quoted the same prices and hence their variance to estimate is the same. So, the values are getting aggregated and the Vendor names are also not populating. How to fix this issue? I have tried looking into other arguments in the Rank function like mode and fmt which I heard could help me out but so far I wasn't able to figure out how to use them here. I'm attaching the QV file for your reference. Thanks in advance!

stalwar1‌ kaushik.solanki‌ tresesco‌

Re: Aggregating two dimension values with same Rank

ogautier62 — Fri, 29 Jun 2018 09:22:33 GMT

to distinguish ties may be two ideas :

use rank(......) & VendorID to agregate

or use : num(rank(sum(sales),4)) there's no tie

regards

Re: Aggregating two dimension values with same Rank

mrthomasshelby — Fri, 29 Jun 2018 09:36:55 GMT

Thanks Olivier for the response. I'm already aggregating on rank and Vendor ID. Your 2nd idea works fine but it's kind of misrepresenting the data I guess. Worst case, I can use this forumla I guess. Thanks again Olivier. Appreciate it.

Re: Aggregating two dimension values with same Rank

mrthomasshelby — Fri, 29 Jun 2018 10:13:32 GMT

Hi Olivier. I have used your second idea along with the fmt argument value 1(Low Value on all rows). I guess this will work. Thanks a lot for the help again!