Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mwalter_yopeso
New Contributor III

Tasks aggregated, split and counted

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

1 Solution

Accepted Solutions

Re: Tasks aggregated, split and counted

Did you try this?

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

10 Replies

Re: Tasks aggregated, split and counted

May be you want this

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

Re: Tasks aggregated, split and counted

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?

mwalter_yopeso
New Contributor III

Re: Tasks aggregated, split and counted

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.

Re: Tasks aggregated, split and counted

Did you try this?

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

mwalter_yopeso
New Contributor III

Re: Tasks aggregated, split and counted

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
Contributor

Re: Tasks aggregated, split and counted

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

1.PNG

mwalter_yopeso
New Contributor III

Re: Tasks aggregated, split and counted

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.

Re: Tasks aggregated, split and counted

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?

mwalter_yopeso
New Contributor III

Re: Tasks aggregated, split and counted

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.

Community Browser