How do I pull out the Day of Week from a Date File (plus average counts)

[#1]

I have a data set and I want to pull a couple of different things out.

First of all - I have the month already as a separate field (example:  1,2,3)

I would like to pull out the 'day of week' from a date format loaded as:    mm/dd/yyyy

I have data spread over a year.

I also have 'hour' of incident as a separate field.

I can create a chart that displays the number of incidents for each hour (and each month).

What I want is:

To be able to find out the 'average' number of incidents for each hour (through the dataset), and also by day of week and month

So: in the end, I can have a chart(s) that will show average # of incidents (count) by hour /day/ month.

Any help would be appreciated.

[swuehl]

Hi,

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,

etc.

In your chart, you could use any of them as dimension and then have an expression like this for calculating the average:

= avg(INCIDENTFIELDNAME)

That could already be most of the work.

Regards,

Stefan

[#3]

Thank you

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

Avg ()

Seems to show no data to display

Knowing the Data. I want to count the number of incidents and then find the average count.

I can “‘count()”

Can I add more than one expression?

If I add Avg() it only shows at 1900 hrs and it appears to be incorrect...

Any thoughts?

[swuehl]

Ah, ok,

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?

[#5]

This is getting so close to being correct.

Unfortunately the [Incident Date] is in a format of mm/dd/yyyy HH:mm:ss

So the unique date needs to be the mm/dd/yyyy only (we need to remove the HH:mm:ss}

[#6]

This is getting so close to being correct.

Unfortunately the is in a format of mm/dd/yyyy HH:mm:ss

So the unique date needs to be the mm/dd/yyyy only (we need to remove the HH:mm:ss}

[#7]

You can try this

date( MakeDate(left( [Incident Date] ,2), mid( [Incident Date] ,5,2 ),right( [Incident Date] ,4 )),'MM/DD/YYYY') as [New Date]

I hope it helps!!

[#8]

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 ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD

month() as Month,

weekday() as Weekday,

day() as Day,

,

,

[swuehl]

If your incident date is recognized as timestamp, I would use something like

date(daystart(Timestamp))

yes, put it in your load script:

...

[Incident Date] as [Incident Timestamp]

date(daystart([Incident Date])) as [Incident Date]

...

(renaming to Timestamp and setting date only as Date)

[#10]

Try this in your expresion:

= Count (Distinct(CAD Incident)) / Count (distinct all(CAD Incident))

or

Avg(Count( Distinct(CAD Incident)))