first make sure that your date loaded is really parsed into date type.
As a test, if you use
=Date(YOURDATEFIELD, 'YYYY-MM_DD') as expression, your dates should be converted to that date format.
If you got problems here, have a look at the date#() function to parse in your date field.
If you have field of data type, you could use all date functions of QlikView (have a look also to the help file):
Add to your data load in the script
month(YOURDATEFIELD) as Month,
weekday(YOURDATEFIELD) as Weekday,
day(YOURDATEFIELD) as Day,
In your chart, you could use any of them as dimension and then have an expression like this for calculating the average:
That could already be most of the work.
So, I have the months and days working well...
But the average doesn’t seem to be working.
If in a dimension I select the “Incident Hour” field
Then in the expressions the
Seems to show no data to display
Knowing the Data. I want to count the number of incidents and then find the average count.
Can I add more than one expression?
image003.png 21.0 K
CAD incident # is probably an ID for the incident event. You don't want to average the IDs, right (though I think you should see some results. Please check in a straight table (you could easily switch your chart type to a straight table and back to bar graph), that there are no results except for 19:00.
Anyway, I think you want the count of incidents (as you already have) divided by the number of days for which we have collected the data, put them into the hours buckets and counted them.
So I think the average incidents per hour of a day (using hour as dimension) is something like:
=count([CAD Incident #]) / count(total distinct Date)
(count incidents in that hour bucket and divide by the total distinct dates (total because we want to get all dates, not only the dates that contribute to the buckets count (think of days with no incidents, those should also be considered for the average).
Does this looks ok?
Thanks for the help. I am obviously a newbie at this.
So one more question –
Do I put this in the Edit script section?
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';