Probably a Set Anlysis solution

Anonymous — Wed, 20 Apr 2016 16:53:24 GMT

Let's assume I have 3 sales rep selling shoes, socks and trousers.

Sales rep 1 has sold

10 shoes

10 socks

Sales rep 2 has sold

10 shoes

10 trousers

Sales rep 3 has sold

20 shoes

20 trousers

My data is something like :

Sales Rep	Shoes sold	Socks sold	Trousers Sold
1	10	10
2	10		10
3	20		20

And I want to represent this in a basic stacked bar chart representing the average number of each item sold by my sales reps

With this:

Dimension : Item Sold

Expression : sum(nbr items sold)/count(distinct sales rep)

I will get for the trousers part : 15 trousers (sold on average by the sales rep). This 15 represents the average number of trousers sold by my sales rep who actually sell trousers.

Now, some of my clients would like a different number : the number of trousers sold on average by my sales rep including those who don't actully sell trousers (in this case it would be 10 + 20 / 3 (sales rep) i.e. 10 (instead of 15).

How do I manage to do that in my bar chart? (it means dividing sum(nbr items sold) by the total number of sales rep including those who have sold an amount of 0)

Thanks in advance

Re: Probably a Set Anlysis solution

sunny_talwar — Wed, 20 Apr 2016 17:11:33 GMT

Your dimension name and expression don't seem to match with the data you have provided. Are you sure your data looks like the way it is displayed above?

Re: Probably a Set Anlysis solution

swuehl — Wed, 20 Apr 2016 17:16:14 GMT

Maybe just like

Sum(Qty) / Count(DISTINCT TOTAL [Sales Rep])

(having used a crosstable LOAD to get your data into a straight table format)

edit:

Or create a variable vSalesRepTotal

=Count(DISTINCT [Sales Rep])

and then

=Sum(Qty) / vSalesRepTotal

topic Probably a Set Anlysis solution in QlikView

Probably a Set Anlysis solution

Re: Probably a Set Anlysis solution

Re: Probably a Set Anlysis solution