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

    Need help with grouping

    Joshua Diffey
      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
      2. A table of "events" on the car, including the location change.
      Car IDLocationFromLocationToEffectiveDate
      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