3 Replies Latest reply: Feb 17, 2012 10:50 AM by dfuentes RSS

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

      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:

       

      SalespersonVehicleVehicle Sold
      AModel 11
      AModel 11
      BModel 11
      BModel 21
      BModel 21
      CModel 11

       

       

      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.