How to sum only some of the rows with expression? (Herfindahl index calculation)
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:
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.
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'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!