Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
So create a pivot chart with two dimensions....
1) Employee
2) WeekDay
Expression
Count( {$<CompletionDate = {">=$(=Date(Today() - 90))<=Today()"}>} CompletionDatetime)
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
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).
So create a pivot chart with two dimensions....
1) Employee
2) WeekDay
Expression
Count( {$<CompletionDate = {">=$(=Date(Today() - 90))<=Today()"}>} CompletionDatetime)
Thank you very much, this works exactly as I had envisioned it.