How to calculated weights or using aggregates of aggregates over multiple dimensions?

[#1]

I'm just starting in Qlikview and stumbled quit hard upon this problem. I've attached an Excel-file to clarify my goals. In an 'answer' to this post I'll send a .qvw as well, to show you my progress (not) so far.

In the excel-file I constructed a dataset consisting of a number of goods and total costs per line. These are reported with an id (e.g. firm) and category (e.g. type of product). With this data I calculate the total costs per id. And the total costs per id if the costs per item were equal to the average of the costs among all records for that category (weighted by the number).

These two total costs are plotted in a bar-chart.

When I tried to do the same in Qlikview I came to the point that the adjusted costs per category per id were calculated. To sum these is out of my current skill-set, I'm afraid, but I'm open for suggestions.

regards, Simon

[#2]

and the .qvw

as I mentioned before: I'm just starting in Qlikview. I'm using the trial edition of version 9.0

regards

[#3]

Hi Simon,

I'd like to help you, but I still have the Office 2003 installed. The conversion (downwards) fails. Could you post your exel in the old 2003 format?

Regards

Roland

[#4]

Hi Roland,

I attached a .xls-version of the excel-file. Hope you can help me out!

regards,

Simon

[#5]

Thanks Simon,

yuo are using function "SUMIFS". This doesn't exist in 2003. Googled for a workaround, nothing to use quickly, so now I am going to my daughters PC. She has always the latest things, ....

[#6]

Hi Simon,

I am sorry, but I can't solve your problem right now. I am sure, some other guys are online to help you (and me) out of this mess.

Sorry again

Roland

[#7 · Accepted solution]

Hi Simon,

I solved your problem. For easier explaining look at the new testweight.qvw.

- you need in that chart only the dim "id"

- in the expression I am using the aggr-function; the general syntax is "sum(aggr( your current expression ,dimension1,dimension2,etc.))".

Regards

Roalnd

[#8]

Hi Roland,

thanks a lot, this works fine!

I think I'll have to dig a litter deeper into the aggr-function, seems more than usefull to me.

have a nice weekend,

Simon

[#9]

Roland,

I tried your suggestion with a slightly more complex problem, but cannot seem to get it to work. I made a separate post here:

http://community.qlik.com/forums/p/36031/141161.aspx#141161

If you have any idea how I can solve this, I would appreciate it enormously!

[#10]

Hi all

thank you very much for this post...

i have this problem last one month....

now i got the output