Hello everybody,
I want to make a table in QlikSense with Months as Dimensions and counts of Tasks as Indicators. The problem that occures is, that a Task can have multiple entries (from timetracking). When I just count the distinct tasks thats no problem, but now I want to split the tasks by time (less than 30 min, 31 min-60 min, over 61 min) but of course it should be split on the whole Task, not on every distinct entry. The sum of the split tasks has to make up the total count of distinct tasks of course. Here an example:
| Date | Task # | Duration (in min) |
|---|
| 01.06.2017 | 1 | 10 |
| 01.06.2017 | 2 | 50 |
| 01.06.2017 | 3 | 20 |
| 01.06.2017 | 1 | 35 |
| 02.06.2017 | 1 | 10 |
| 02.06.2017 | 2 | 70 |
Right now i get with "Count (distinct [Task #]) " 3 for June, which is perfectly correct. But when I want to split them "Count (distinct {$<Duration={"<=30"}>}[Task #])" and so on, I get (filtered for June) this: 3 Tasks <30 min, 3 Tasks >30<60, 1 Task >60.
What I want is 1 Task <30, 1 Task >30<60, 1 Task >60.
I think the solution is something with the aggr() function, but I couldnt figure it out correctly. Also it would be perfect if the task gets counted on the last day time was tracked (so task 3 on 1.06., task 1 and 2 on 2.06.)
Thank you very much for your support in advance!
Best Regards, Matthias
