Count number of entries meeting monthly criteria

[drew61199]

I'm building a vehicle utilization dashboard and need to highlight entry errors (negative mileage between First of Month and Last of Month and then also the inverse of excessive mileage). Below is the formula I'm using to calculate the excessive mileage (which is considered >3000 miles). 

=RANGEMAX(-1*(FirstSortedValue(DISTINCT(begin_mileage),end_date)-FirstSortedValue(DISTINCT(end_mileage),-end_date)),3000)

The database has individual row per day per vehicle, so thus the columns of begin mileage, end mileage, and end date.

At a vehicle level, this will show me the variance between the beginning and end of the month. I then need to have this roll up by location, geographic territory, etc (multiple hierarchy levels that I plan to highlight as "top opportunities" via bar charts) - ie - a particular state or city has 50 entries for a single month. I cannot simply add a COUNT function, so thinking this formula is going to get a LOT more complicated. I appreciate any insight

[stevedark]

Hi @drew61199 

This is something that I would look to tackle in the load script rather than in the front end, if possible. It will slow down the load script some, but it will be much more performant in the front end - maybe even being the difference between it being able to work or not.

The way to tackle it is to create two new keys within your current data table, VINMonth and VINDate - where you concatenate the Vehicle ID to the month (YYMM) and date (YYMMDD) respectively.

Do a resident load to find the first and last date entry for each month for each VIN. Something like:

tmpFirstAndLast:
LOAD
   VINMonth,
   max(VINDate) as LastInMonth,
   min(VINDate) as FirstInMonth
RESIDENT VehicleUtilisation
GROUP BY VINMonth;

Next left join the flags for first and last onto the source table:

LEFT JOIN (VehicleUtilisation)
LOAD
   LastInMonth as VINDate,
   1 as IsFirstInMonth
RESIDENT tmpFirstAndLast;

LEFT JOIN (VehicleUtilisation)
LOAD
   FirstInMonth as VINDate,
   1 as IsLastInMonth
RESIDENT tmpFirstAndLast;

DROP TABLE tmpFirstAndLast;

Already the flags on the table will make it easier to work out the deltas you want, but I would go even further in the data prep, and work those out in the load also, with another resident load.

MonthlyStats:
LOAD
   *,
   MileageAtEnd - MileageAtStart as MileageInMonth
   ;
LOAD
   VINMonth,
   max(if(IsFirstInMonth=1,Mileage,null()) as MileageAtStart,
   max(if(IsFirstInMonth=1,Mileage,null()) as MileageAtEnd,
   avg(Mileage) as AverageMileageInMonth,
   sum(1) as EntriesInMonth
RESIDENT VehicleUtilisation
GROUP BY VINMonth;

You can add other calculations into this table as well, if required.

I think that with this your original requirement may be dealt with. It doesn't however pick up if there is a drop between one month and the subsequent. In order to do this you would need to do a resident load of the monthly table, with an order by, and peek out the previous months end and compare it with this months start. You could use the same technique to check day by day if there is a drop in mileage and record the daily delta.

Hope that all makes sense and works out for you.

You may also want to check out the UK MOT demo I have linked from this page:
https://www.quickintelligence.co.uk/instant-qlik-sense-app/

Cheers,
Steve

[stevedark]

Actually...

Just re-read back your original requirements, and you mention the data is currently in a database.

You could achieve the same monthly table, with start and end mileage for the month by using nested subqueries and GROUP BY statements in SQL. The advantage of this is that you are moving the processing from the Qlik server to a SQL Server. The end result would be very similar though.

Steve