Creating Groupings of Calculated Dimensions in a Chart Based On Count of Distinct Values

[#1]

Hello, All.

I am very new to QlikView, and I need advice on creating a bar chart based on a complicated (or at least I think it is) set of calculated dimensions and expressions. I have data describing vehicle sales, and I want to see the number of vehicles sold by each distinct salesperson grouped by intervals of vehicles sold. I want buckets of sold vehicles to be displayed as the primary dimension (x-axis) on a bar chart, and I want these buckets to be some number of equally spaced intervals; e.g. 1-3, 4-6, 7-9, etc. I would then like to have the individual salesperson as a secondary dimension displayed as a stacked value. It doesn't matter what vehicle the salesperson sells; it simply matters that they sold a vehicle. Each salesperson should only be connected to one number of vehicles sold, and each salesperson should be connected to a number. Finally, each salesperson should only show up in one bar of the chart.

My data look similar to this:

Salesperson	Vehicle	Vehicle Sold
A	Model 1	1
A	Model 1	1
B	Model 1	1
B	Model 2	1
B	Model 2	1
C	Model 1	1

where I have a field containing non-distinct salespeople, a field with the vehicle type, and a field containing a 1 for each vehicle sold. If the vehicle sold column is summed, it returns the same value as count([Vehicle]). I am hoping that this field somehow helps.

Assume from this example data that I want the main dimension of a graph to be groupings of vehicles sold as "1 to 2" and "3 to 4" so that the first group contains Salesperson A (2 sales) and Salesperson C (1 sale), while the second group contains just Salesperson B (3 sales). I then want the bars to be stacked by salesperson so that the first bar is split to show Salesperson A and C, and the second contains just Salesperson B. I want the expression (y-axis) to display the sum of the counts of sales made so that the first bar is 3 units high (2 from Salesperson A, 1 from C) and the second is also 3 units high (all 3 from Salesperson B).

I tried to make everything as clear as possible, but if I didn't, please ask me for further explanation. My actual dataset has approximately 100 distinct salespeople and approximately 400 vehicles sold. I am thinking that I will bucket the primary dimension into groupings of 3 vehicles sold so that the X-axis displays 1-3, 4-6, 7-9, 10-12, and 13+.

Thanks a lot for any help.

[#2]

Can anyone at least point me in the right direction? Any help at all would be much appreciated.

Thanks again for any help.

[deepakk · Accepted solution]

hi,

I hope the attached application is the solution what you are looking for.

Deepak

[#4]

Very nice. Thank you, Deepak.