How to sum only some of the rows with expression? (Herfindahl index calculation)

[#1]

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.

[IAMDV]

Please can you post QV document with some sample data. I can try this one for you.

Cheers - DV

[#3]

Have you tried with SET Analysis?

[IAMDV]

Just to give you some heads up...You can use Indirect Set Analysis ( I am including little explanation for your understanding, incase you might need)

The functions p() and e() can be used to select the set of possible or excluded values within a field.

p( ) = Possible
e( ) = Excluded

Examples:

Customer = p({<Year={$(=max(Year)-1)}>} Customer)
Returns customers who had an association to last year

sum(({<Year={$(=max(Year))}, Customer = p({<Year={$(=max(Year)-1)} Customer>}) Sales)
Returns this year's sales for customers who had sales last year..

Hope this might help you.

Cheers - DV

[#5]

Well, I don't know what is the set I need. I tried to think if it would help, but the required set differs from row to row depending which product is in question. I don't know how to make that happen.

[#6]

Here's an example file. Currently I get the correct index if I select one of the products. If none of the products are selected the indexes are incorrect.

[IAMDV]

Dear Henry,

Just I wanted to make sure if I had correctly understood the issue. My understanding is as follows...

You have 4 different products :

vesi

maa

tuli

ilma And you wanted to calculate the % of Amount of each product? And it should not return anything when product is not selected. Is this right? I am little confused with the issue. If this is what you need why don't you change the setting for the list box to select atleast one option. (Always one selected value). If this is not what you need please can explain the problem with more detail. I think set analysis is the solution but I need more understanding. Cheers - DV

[#8]

Nope, that's not what I was trying to do.

I have the four different products: vesi, maa, tuli & ilma and I wanted the table to show the following values:

ilma	0.3073
maa	0.3152
tuli	0.3059
vesi	0.2899

Those are the correct index values for the products, and I can calculate them so that they show when only one product is selected.

To make this very clear: When nothing is selected the index value shown on the table for "ilma" is 0,020. That is incorrect value. If I select the "ilma" it shows 0,3073, which is the value I want. I want the correct values listed for all of those products to be listed there at the same time. I just don't know how. I can only calculate them one by one by selecting the products.

EDIT: Background information on the index that is being calculated there: http://en.wikipedia.org/wiki/Herfindahl_index It's simple but I can't transfer it to QV thinking.

[IAMDV]

Hi Henry,

I had understood the issue and thank you very much for clear explanation. hmmm! It is challenging for sure. I thought of solution but it is not very clever in the sense if we have more products. I thought we can create a variable with nested IF statements. Something like this...

IF(Product = 'vesi' , sum({<Product = {vesi}>} Amount), IF(Product = 'maa' , sum({<Product = {maa}>} Amount), IF(Product = 'tuli' , sum({<Product = {tuli}>} Amount), IF(Product = 'ilma' , sum({<Product = {ilma}>} Amount)))))

Then we will get the subtotals for each products and we can use teh same variable in the orginal expression. I mean in this expression...

sum( aggr( if( rank( sum($(vSubTotalAmount)))<=50,sqr(sum($(vSubTotalAmount))/sum(TOTAL Amount)), 0), Supplier))

Let me know if this makes sense...

Cheers - DV

[#10]

Yes, with if statements it can be done, but then it would be hard coded and since I'm working on to build a template for different projects, it is not very usable. Any drilldown functionalities would be also out, and these are very important too. Thank you for your time and ideas, I was afraid that it wouldn't work since I couldn't find anything from these forums that had examples of similar way to dynamically change the set in question.

One thing I was considering was scripting. I haven't really looked into that yet but would it be possible to make it work with that? I mean, just run some cleverly written macro that would collect the correct values that need to be looked for and do the proper calculations with SQL queries for example?