topic Time Differences put into Categories in QlikView

Time Differences put into Categories

rosey13478 — Thu, 23 Jun 2011 15:36:24 GMT

I have two date/time fields (BATime & AppTime), I need to be able to count how many people are in the following categories:

BATime is less than AppTime

BATime is within +/- 5 mins of AppTime

BATime is up to 30 mins after AppTime

BATime is up to 60 mins after AppTime

BATime is greater than 60 mins after AppTime

I have tried various calculations using interval to find the difference in minutes between the BATime and AppTime, the problem I am having is putting the time differences into the above categories. I am going to show this info in a pie chart....hopefully!

Thanks

Time Differences put into Categories

Thu, 23 Jun 2011 18:51:29 GMT

Hi rosey,

if your BATime and AppTime are both timestamps (in same table), you can easily calculate with them like this:

Diff = AppTime-BATime

With this in mind try to start (as an exercise) creating a straight table with for ex. two expressions "DiffTime" and "Category" like this:

"DiffTime":

AppTime-BATime (format it as an "interval" for better understanding of your expr "Cat")

and

"Cat":

if((AppTime-BATime) < '00:00:00','A',

if((AppTime-BATime) < '00:05:00', 'B',

if((AppTime-BATime) < '00:30:00', 'C', 'D'

)))

If you are familar with this, I would suggest you to put the whole calculating into the load-script. Then you are able to use your Time-Cat whereever you want, for ex. as an dimension in a pie chart ....

HtH

Roland

Time Differences put into Categories

rosey13478 — Fri, 24 Jun 2011 07:53:02 GMT

Thanks Roland,

I thought it should have been relatively simple, just couldn't work out the correct format. Your suggestion worked perfectly.

Thanks Again