Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

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.

13 Replies
Not applicable
Author

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.

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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!

IAMDV
Luminary Alumni
Luminary Alumni

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