10 Replies Latest reply: Jun 26, 2017 7:08 AM by Sunny Talwar RSS

    Tasks aggregated, split and counted

    Matthias Walter

      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:

       

      DateTask #Duration (in min)
      01.06.2017110
      01.06.2017

      2

      50
      01.06.2017320
      01.06.20171

      35

      02.06.2017110
      02.06.2017270

       

      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