Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

9 Replies
Not applicable
Author

and the .qvw

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

regards

Not applicable
Author

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

Not applicable
Author

Hi Roland,

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

regards,

Simon

Not applicable
Author

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, ....

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

Hi all

thank you very much for this post...

i have this problem last one month....

now i got the output