5 Replies Latest reply: Jul 9, 2013 2:39 AM by Rob Wunderlich RSS

    Need help with grouping

      Hi All,
      I have what I thought was a simple problem that has turned out to be a little trickier than expected and was hoping someone could assist. Basically I have a database of cars and them being hired out to "clients". The database consists of 2 tables of relevance.
      1. The table of cars which includes its current location and a tonne of fields that are not required for this example.
      Car IDCurrent Location
      car1base
      car2x
      car3y
      2. A table of "events" on the car, including the location change.
      Car IDLocationFromLocationToEffectiveDate
      car1basex02/01/2013
      car1xy07/03/2013
      car1ybase22/03/2013
      car2basex02/01/2013
      car2xbase09/02/2013
      car3basey05/03/2013
      car3ybase07/03/2013
      car4basex22/01/2013
      car4xz27/01/2013
      car4zbase28/01/2013
      I want a chart (line graph or bar chart, not really fussed at this stage) that shows me month by month, how many cars were being "used" at the end of the month. The chart should show 3 months in this example as there is only 3 months worth of data. The numbers should be
      Jan = 3 (car4 not included as it was back at base before the end of the month, the other 3 were out at clients)
      Feb = 1 (car1 was the only car out at clients)
      Mar = 0 (car1 was returned during the month)
      I can't work out how to get the above results in qlikview though. A couple of issues being that in the above example car1 did not have an "event" in feb, it was at a client for all of feb.
      I hope this makes sense, any help would be greatly appreciated