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!
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 ....
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 ....
