Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

msmcilvride
New Contributor II

Count all datetime instances that occur on Monday?

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

CompletionDatetimeCompletionDateEmployeeCondition 1Condition 2
2017-09-25 10:32:48.1842017-09-25Employee1Yes

No

2017-09-26 09:22:17.4472017-09-26Employee2YesNo
2017-09-28 12:08:54.1062017-09-28Employee3NoNo
2017-10-02 09:28:23.8572017-10-02Employee4YesYes

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:

EmployeeMondayTuesdayWednesdayThursdayFriday
Employee1157208194177201
Employee210621018420098
Employee32770101134137
Employee402122301170

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Count all datetime instances that occur on Monday?

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

1) Employee

2) WeekDay

Expression

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

4 Replies
MVP
MVP

Re: Count all datetime instances that occur on Monday?

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

msmcilvride
New Contributor II

Re: Count all datetime instances that occur on Monday?

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

MVP
MVP

Re: Count all datetime instances that occur on Monday?

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

1) Employee

2) WeekDay

Expression

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

msmcilvride
New Contributor II

Re: Count all datetime instances that occur on Monday?

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

Community Browser