Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

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
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
5 Replies
New Contributor III

Re: Need help with grouping

on reflection I believe the 1st table in the above example is not relevent to this example.

added a very simple qlikview file with the inline statement done if it is useful for anyone

Not applicable

Re: Need help with grouping


have you tried something like:

count(if(LocationTo=base;0;1))  ?

This should give you a count of all the lines where the "LocationTo" is not equal base. If I understood your problem, that's what you want?

Greeting Wiley_E

Not applicable

Re: Need help with grouping

oh... correction:

I just recognized, that my last answer would give you counts for the movements during the months....

maybe you could combine the Count-statement above with a search for the "maximum" date stamp per month....

Greetings Wiley_E

New Contributor III

Re: Need help with grouping

Hi Wiley_e_

thanx for the response, unfortunately the problem is that using the above example neither of these options would tell me that car1 was being used for March as it was out for over a month and there were no "events" in march

Re: Need help with grouping

Attached is one possible solution.

The first step is to expand the events table into a row for every date in the interval.  Henric;s blog post


describes this in detail. For this example, I used a QVC (http://qlikviewcomponents.org) routine to expand the table:

  CALL Qvc.ExpandInterval('CarEvents', 'EffectiveDate', '', '[Car ID]');

The next step is to flag the month end dates in the table. I chose to create a new field for those rows that have a month end date:

Right JOIN (CarEvents)

LOAD Distinct *

          ,if(EffectiveDate = floor(MonthEnd(EffectiveDate)), date(EffectiveDate,'YYYY-MMM')) as MonthEnd



At this point, you could summarize the <>base [Car ID] count in the script. I chose to do the counting in the chart, anticipating that you may want to use additonal selectons like "Make=Toyota".  The chart Dimension is the previously created "MonthEnd" field , the expression is:

=sum(0) +

count({<LocationTo=-{"base"}>}[Car ID])

The sum(0) bit is a trick to force inclusion of the months that have zero cars.




Community Browser