topic Count all datetime instances that occur on Monday? in App Development

Count all datetime instances that occur on Monday?

Anonymous — Mon, 02 Oct 2017 15:50:19 GMT

I am working with a dataset that looks something like this:

CompletionDatetime	CompletionDate	Employee	Condition 1	Condition 2
2017-09-25 10:32:48.184	2017-09-25	Employee1	Yes	No
2017-09-26 09:22:17.447	2017-09-26	Employee2	Yes	No
2017-09-28 12:08:54.106	2017-09-28	Employee3	No	No
2017-10-02 09:28:23.857	2017-10-02	Employee4	Yes	Yes

I can count the number of completed tasks by counting the number of values in the CompletionDatetime column (the example table above has 4). I need to create a report that aggregates CompletionDatetime by weekday for the past 90 days. So, if CompletionDatetime has a value, it should be counted as 1 for that weekday. The final result would look like this:

Employee	Monday	Tuesday	Wednesday	Thursday	Friday
Employee1	157	208	194	177	201
Employee2	106	210	184	200	98
Employee3	277	0	101	134	137
Employee4	0	212	230	117	0

I have figured out how to count only the values in the past 90 days:

Count( {$<CompletionDate = {">=$(=Date(Today() - 90))<=Today()"}>} CompletionDatetime)

But I am stumped as to how to add a weekday condition to it.

Re: Count all datetime instances that occur on Monday?

sunny_talwar — Mon, 02 Oct 2017 16:19:21 GMT

Create a new field in the script using WeekDay function....

LOAD WeekDay(CompletionDate) as WeekDay,

....

FROM ....;

and then use WeekDay as your dimension and the above expression

Re: Count all datetime instances that occur on Monday?

Anonymous — Mon, 02 Oct 2017 16:41:38 GMT

Thank you very much, this works. Unfortunately, I left out the dimension I needed in my explanation, for which I apologize. I will update my post to include the dimension needed (Employee).

Re: Count all datetime instances that occur on Monday?

sunny_talwar — Mon, 02 Oct 2017 16:47:10 GMT

So create a pivot chart with two dimensions....

1) Employee

2) WeekDay

Expression

Count( {$<CompletionDate = {">=$(=Date(Today() - 90))<=Today()"}>} CompletionDatetime)

Re: Count all datetime instances that occur on Monday?

Anonymous — Mon, 02 Oct 2017 17:03:42 GMT

Thank you very much, this works exactly as I had envisioned it.