Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

diffeyj
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
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
5 Replies
diffeyj
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

Hi,

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

diffeyj
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

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

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

RESIDENT CarEvents

;

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.

-Rob

http://masterssummit.com

http://robwunderlich.com

Community Browser