Tasks aggregated, split and counted

[Anonymous]

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

[sunny_talwar]

May be you want this

Sum(Aggr(If(Sum(Duration)<=30, 1, 0), MonthField, [Task #]))

[sunny_talwar]

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

Not sure I understand this part? Is this for the by duration count as well? If it is, then the count will change to 2 tasks <30 and 1 task >60, right?

[Anonymous]

Hi Sunny, the count should be on the [Task #]. Task 1 takes overall 55 min, Task 2 120 min and Task 3 20 min.

Attached is a Screenshot how it looks like right now. As you can see, the SML change num (total count) is 12 less then the sum of Small, Medium and Large. I dont know if I got your question right.

[sunny_talwar · Accepted solution]

Did you try this?

Sum(Aggr(If(Sum(Duration) <= 30, 1, 0), MonthField, [Task #]))

[Anonymous]

I seems to go in the right direction, thank you! I need to go back to my desk and test it a little, will come back to you!

[kvanandan1]

Hi ,

I have used below function to get the count per Duration (converted into a bucket <30, >30<60, >60)

count( Aggr(count(distinct [Task #]),Bucket,[Date.autoCalendar.Month]))

Bucket is based on above logic <30,>30 etc...

If you are satisfied, please mark it as correct answer.

Thanks.

Output will be

[Anonymous]

Hi Sunny, it works fine now, thank you very much! But now I have another Problem: I also want to get the total Duration as an output. I tried this: Sum(Aggr(If(Sum(Duration)>0.333, Sum(Duration), 0), Month, [Task #])) but it doesnt work.

I added the Dataset as an Attachment. It would be great if you can support me here again.

[sunny_talwar]

What did the total showed? Null or is it not even showing the total row? I am trying to understand what the issue might be? Also, what all dimensions do you have in your chart?

[Anonymous]

Sorry, that was my fault. I updated the .csv but not Qliksense, so some data was missing there. Seems to work! Thank you very much.