topic Re: Average for aggregate data in QlikView

Average for aggregate data

Thu, 13 Aug 2015 08:54:19 GMT

I have a table of data that consists of suppliers, where they supply and a score (plus a lot more, but irrelevant for this question). What I'm trying to do is to calculate the average score per supplier and present it in a straight table. I also want the user to be able to display the information per site or per supplier.

A supplier can only have one score regardless of how many sites it delivers to
A total average need to be calculated and the average should disregard the number of sites (i.e. a supplier that only supplies to one site will have the same weight as one supplying to 3 sites)

This should be straight forward, but I get the wrong result. Either I am able to get the correct average by using an aggregate, but then the score will only show up on one site or I am able to getthe correct score per site, but then the average is screwed up...

Below is how I want it to show up (the first is the raw data and the the last two tables my desired result), but I don't get it to work. I'm attaching a small file with the data.

Any ideas on how to solve it (I think it should be really easy and get really frustrated that I can't solve it myself...)

Supplier	Site	Score
AAA	1	5
AAA	2	5
AAA	3	5
BBB	1	2
CCC	1	2
DDD	2	4
DDD	3	4
EEE		5

Supplier	Site	Score
Average		3,6
AAA	1	5
AAA	2	5
AAA	3	5
BBB	1	2
CCC	1	2
DDD	2	4
DDD	3	4
EEE		5

Supplier	Score
Average	3,6
AAA	5
BBB	2
CCC	2
DDD	4
EEE	5

Re: Average for aggregate data

sunny_talwar — Thu, 13 Aug 2015 08:58:25 GMT

May be try this:

If(Dimensionality() = 0, Avg(Aggr(Avg(Score), Supplier)), Avg(Score))

Re: Average for aggregate data

MK_QSL — Thu, 13 Aug 2015 09:00:35 GMT

Go to expression tab

Selection your expression

Go to Total Mode in the same tab

Select

Average of Rows

Re: Average for aggregate data

Thu, 13 Aug 2015 11:20:11 GMT

Thanks for the tip. I tried it and it does work in this instance. The problem is that the data is a little bit more complex than that and that I also need some other aggregations (such as category code which a few suppliers belong and then I want the data to aggregate on the supplier level).

So unfortunately, still no luck...

Re: Average for aggregate data

Thu, 13 Aug 2015 11:22:14 GMT

Thanks, but I don't see how that would work. A row average is what I am trying to avoid (since that would give me the wrong answer).

What I really would need would be some way to calculate the sum distinct (per supplier number) in the same manner as count distinct works. I thought the aggr would do that, but it only puts the value on the first instance the supplier number shows up...

Re: Average for aggregate data

sunny_talwar — Thu, 13 Aug 2015 11:24:30 GMT

Would you be able to share a more representative sample of the data you are working with? May be there is something which can be done to make it work.

Re: Average for aggregate data

MK_QSL — Thu, 13 Aug 2015 11:27:25 GMT

Provide sample documents which is not working as per our reply alongwith the result you are looking for..

Re: Average for aggregate data

Thu, 13 Aug 2015 11:39:08 GMT

Hi,

Here is some more data (now with category). Hope this explains it better.

Re: Average for aggregate data

Thu, 13 Aug 2015 11:39:28 GMT

Hi, added a new file

Re: Average for aggregate data

Thu, 13 Aug 2015 11:39:47 GMT

Hi,

Added a new file with some more data.

Re: Average for aggregate data

MK_QSL — Thu, 13 Aug 2015 11:45:59 GMT

What result you are looking for?

Re: Average for aggregate data

Thu, 13 Aug 2015 11:51:41 GMT

The average for the categories shoud be:

Orange: 3.5

Apple: 3.5

Banana: 4

The average per site should be:

Site 1: 3

Site 2: 4.5

Site 3: 4.5

The full total should be 3.6.

And when I choose to show the suppliers, the correct value should be shown in each cell.

Or to put it in more general terms - the averages should always be based on the supplier and a supplier should only be regarded once (even though it may supply several sites).