Percentage change from previous day.

Thu, 12 Aug 2010 11:22:50 GMT

Hi All,

I need QlikView to find the number of calls in a particular hour of a particular day and compare it to the same hour in the previous day and provide me with the percentage increase/decrease.

For example.

13:00 on 10/02/2010 - 100 Calls

13:00 on 09/02/2010 - 80 Calls. So the percentage increase would be 25%.

The data in Excel is as follows;

Column for Date, column for time (Hourly) and column for Number of Calls.

Ideally the hour would not be fixed and the calculation would change depending on which hour the user selects.

Apologies if unclear as I'm new to QlikView and cannot post my data file/QV document due to company policy.

Thanks in advance

James

Percentage change from previous day.

johnw — Thu, 12 Aug 2010 23:54:54 GMT

Even when it's against company policy to post your document, it can be very helpful if you post a sample QlikView document with a very simplified version of the data you're using, along with the results you expect. For instance, in the attached solution, I use this to generate some sample data:

Calls:
LOAD
recno() as ID
,timestamp(date#(20100801,'YYYYMMDD')+floor(rand()*12*24)/24) as Hour
AUTOGENERATE 10000
;

My suggested solution is to do something fairly complicated, which is to build a table that links today's hours to themselves AND to yesterday's hours, with flags letting you know what you're looking at:

AsOf:
// For each available hour, tie it to itself with the "Today?" flag.
LOAD
timestamp(fieldvalue('Hour',iterno())) as AsOfHour
,timestamp(fieldvalue('Hour',iterno())) as Hour
,1 as Today?
AUTOGENERATE 1
WHILE len(fieldvalue('Hour',iterno()))
;
// For each available hour, tie it to 24 hours ago with the "Yesterday?" flag.
CONCATENATE (AsOf)
LOAD
AsOfHour
,timestamp(AsOfHour-1) as Hour
,1 as Yesterday?
RESIDENT AsOf
;
// Only keep the connection to yesterday if the hour actually exists yesterday.
INNER JOIN (AsOf)
LOAD AsOfHour as Hour
RESIDENT AsOf
;

Then use AsOfHour as your table dimension instead of Hour, and use the two flags to control what you include in your count of calls. There are simpler solutions, but I think this is more robust than the simpler solutions that I'm aware of.

topic Percentage change from previous day. in QlikView

Percentage change from previous day.

Percentage change from previous day.