Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with Product, Supplier and Amount columns. I'm trying to make a table chart showing the Product as one field and Herfindahl index as another. In order to count the index in question I would need to get the sum of Amount's for each Supplier that is supplying the product. This is what I tried, but it only works if only one of the Products is selected and then it counts it correctly for that one Product:
sum( aggr( if( rank( sum(Amount))<=50,sqr(sum( Amount)/sum(TOTAL Amount)), 0), Supplier))
Is there a way to expand from here and somehow have it calculate that index for all the Products without selections? The problem is that I don't know how to restrict the sum() to include only the relevant rows.
Or could there be a way to do this with some kind of calculated dimension? I haven't really looked into them yet so I'm not sure what can be accomplished with them.
Hi Henry,
Thanks for the update. I am very keen to solve this one. It is quite interesting and I haven't explored this area of QV. You are right! Even I am thinking of calculated dimension instead of relying on scripting.
I have an idea and let me know if this helps you... We can subtotal the amounts based the dimension values, something like this...
=sum(total <Product> Amount)
Can you please use this idea and integrate in the orginal expression?
I'll forward to hear from you.
Cheers - DV
I've cracked this one finally. Had a fresh thought about this and analysed a little bit more what the aggr() function actually does and came up with this: =sum(aggr(sqr(sum(TOTAL <Product, Supplier> Amount)/sum(TOTAL <Product> Amount)), Product, Supplier))
Clearly this was greatly inspired by your idea DV, so thank you for that!
Hi Henry,
I am very happy to know that you got it worked. I am sure there is good scope with Set Analysis. Just need to get our head around the syntax and the brackets.
Thank you for the points 🙂
Happy QVing!!