Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
rathorep
Contributor III
Contributor III
‎2020-03-15 09:01 AM

Group by in front end

Below is my dummy data, 

my requirement is to calculate

Ticket number: Modified Group name: and Total time spent by each group on per ticket.

In below data we have ticket number(just filtered for 1 ticket), Modified group name(Group who modifies the ticket),

Modified Date(Date on which ticket has been modified by group.

Last column i am calculating difference of time on each line. i.e. when Modified date when Ticket first assigned to next group(B) - Modified Date when ticket first assigned to group A

I was trying to sum up last column group by Ticket number and Modified group name by formula 

Sum(Aggr((interval((Below(Total ([Modified Date]),1,1)-[Modified Date]),'hh.m')),[Modified Group Name],[Ticket Number]))

But getting only zero for each line.

Please help!

Ticket NumberModified DateModified Group Nameinterval((Below(Total ([Modified Date]),1,1)-[Modified Date]),'hh.m')
178110/3/2019 12:11A0.46
178110/3/2019 12:58B3.11
178110/3/2019 16:09C14.16
178110/4/2019 6:25A126.52
178110/10/2019 5:40B16.22
178110/11/2019 7:37C25.57
2,926 Views
42 Replies
sunny_talwar
MVP
MVP
‎2020-04-01 01:42 PM

In response to rathorep

I am not sure I follow. Again, it would help me if you can provide the output you are hoping to get from the data provided.

687 Views
0 Likes
rathorep
Contributor III
Contributor III
‎2020-04-01 03:03 PM
Author

In response to sunny_talwar

Data :

Ticket NumberTeam NameModified Date Formula used for 4th column
1781A10/3/19 12:11 PM0:47C3-C2
1781B10/3/19 12:58 PM3:11C4-C3
1781B10/3/19 4:09 PM14:16C5-C4
1781S10/4/19 6:25 AM6:53C6-C5
1781G10/9/19 1:18 PM16:22C7-C6
1781S10/10/19 5:40 AM1:57C8-C7
1781S10/11/19 7:37 AM7:33C9-C8
1781G10/25/19 3:10 PM17:49C10-C9
1781B10/28/19 8:59 AM3:18C11-C10
1781S10/28/19 12:17 PM20:51C12-C11
1781D10/29/19 9:08 AM0:39C13-C12
1781G10/29/19 9:47 AM22:27C14-C13
1781S10/30/19 8:14 AM5:08C15-C14
1781S10/31/19 1:22 PM22:00C16-C15
1781G11/12/19 11:22 AM0:02C17-C16
1781G11/12/19 11:24 AM3:48C18-C17
1781B11/12/19 3:12 PM16:23C19-C18
1781S11/13/19 7:35 AM5:30C20-C19
1781S11/14/19 1:05 PM1:58C21-C20
1781G11/14/19 3:03 PM19:20C22-C21
1781S11/15/19 10:23 AM1:41C23-C22
1781S11/15/19 12:04 PM3:49C24-C23
1781G11/26/19 3:53 PM15:25C25-C24
1781S11/27/19 7:18 AM6:19C26-C25
1781S11/27/19 1:37 PM10:38C27-C26
1781A12/3/19 12:15 AM0:00C28-C27

 

 

Required calculation after above step:

Ticket NumberTeam NameModified DateValue calculated in above table
1781A10/3/19 12:11 PM0:47
1781A12/3/19 12:15 AM0:00
  Total0:47

 

 

Ticket NumberTeam NameModified Date 
1781B10/3/19 12:58 PM3:11
1781B10/3/19 4:09 PM14:16
1781B10/28/19 8:59 AM3:18
1781B11/12/19 3:12 PM16:23
  Total13:08

 

 

Ticket NumberTeam NameModified Date 
1781D10/29/19 9:08 AM0:39
  Total0:39

 

 

Ticket NumberTeam NameModified Date 
1781G10/9/19 1:18 PM16:22
1781G10/25/19 3:10 PM17:49
1781G10/29/19 9:47 AM22:27
1781G11/12/19 11:22 AM0:02
1781G11/12/19 11:24 AM3:48
1781G11/14/19 3:03 PM19:20
1781G11/26/19 3:53 PM15:25
  Total7:48

 

 

Ticket NumberTeam NameModified Date 
1781S10/4/19 6:25 AM6:53
1781S10/10/19 5:40 AM1:57
1781S10/11/19 7:37 AM7:33
1781S10/28/19 12:17 PM20:51
1781S10/30/19 8:14 AM5:08
1781S10/31/19 1:22 PM22:00
1781S11/13/19 7:35 AM5:30
1781S11/14/19 1:05 PM1:58
1781S11/15/19 10:23 AM1:41
1781S11/15/19 12:04 PM3:49
1781S11/27/19 7:18 AM6:19
1781S11/27/19 1:37 PM10:38
  Total22:17

 

So final result should be:

Ticket number Team Total of each team for this ticket
1781A0:47
1781B1:55
1781D9:21
1781G7:48
1781S22.17

 

 

682 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-04-02 10:48 AM

In response to rathorep

This is what I am not sure about... I can get the numbers you posted for the individual rows... but how are you calculating the total? What is the calculation here for the total?

image.png

 

667 Views
0 Likes
rathorep
Contributor III
Contributor III
‎2020-04-02 01:59 PM
Author

In response to sunny_talwar

This was my doubt actually, I am not able to calculate the total.. what i was trying is.. as we do in sql:

select 'Ticket Number,'Team Name',sum(4th column as calculated above)

from table group by 'Team number', 'Team Name';

 

I want to implement this in qliksense. 

662 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-04-02 02:32 PM

In response to rathorep

But how can Sum of four numbers (positive numbers) where one of the number is 16 be 13? Shouldn't it be at least 16? I mean it is basic math.

659 Views
0 Likes
rathorep
Contributor III
Contributor III
‎2020-04-03 12:34 AM
Author

In response to sunny_talwar

Yes you are right but as the calculated field format is hours and minutes so may be it got converted in excel

. but if you can calculate the total we can check it

653 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-04-03 07:21 AM

In response to rathorep

For B, I am getting 37 hours and  8 minutes.

    16 hours and 23 minutes
+ 14 hours and 16 minutes
    30 hours and 39 minutes
+ 03 hours and 11 minutes
    33 hours and 50 minutes
+ 03 hours and 18 minutes
    37 hours and 08 minutes

image.png

and I guess your next question would be, what is the expression that I used and yet again I will tell you that it is the same expression as pointed above

 

Interval(
  Sum(Aggr(
    
    Interval(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.m')

  , [Ticket Number], ([Modified Date], (NUMERIC))))
, 'hh:mm')

 

Taking a step back, do you really know what you want here?

646 Views
0 Likes
rathorep
Contributor III
Contributor III
‎2020-04-09 12:37 PM
Author

In response to sunny_talwar

Hello sunny, Finally its good for me now:

Interval(
sum(Aggr
(Interval
(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.mm') , [Ticket Number],([Modified Date], (NUMERIC)))), 'hh:mm')

Now I am having another doubt, as I have multiple tickets , So when we are doing below row modified date minus current modified date row..it works correct but when one ticket lines gets over and another tickets comes, there will be negative value as small date minus big date is happening. I want to use Rangemax to this expression to make "0" where negative values are coming . please help me to replace negative value by 0.

Ticket NumberTeam NameModified DateInterval(sum(Aggr(Interval(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.mm') , [Ticket Number],([Modified Date], (NUMERIC)))), 'hh:mm')
Total 909.52
17672486A9/13/2019 6:01297:25
17672486C9/25/2019 15:26112:49
17672486A9/30/2019 8:1633:14
17672486C10/1/2019 17:3045:55
17672486C10/3/2019 15:261:44
17672486D10/3/2019 17:110:30
17672486C10/3/2019 17:41139:06
17672486B10/9/2019 12:480:09
17672486C10/9/2019 12:57162:49
17672486G10/16/2019 7:470:10
17672486C10/16/2019 7:5830:31
17672486G10/17/2019 14:300:42
17672486C10/17/2019 15:1219:51
17672486O10/18/2019 11:041:54
17672486C10/18/2019 12:580:30
17672486A10/18/2019 13:2965:49
17672486C10/21/2019 7:187:57
17672486O10/21/2019 15:1521:29
17672486C10/22/2019 12:4519:34
17672486O10/23/2019 8:190:03
17672486C10/23/2019 8:2350:28
17672486C10/25/2019 10:521:32
17672486O10/25/2019 12:242:20
17672486C10/25/2019 14:44-727:56
17674710A9/13/2019 10:040:31
17674710C9/13/2019 10:3674:21
17674710A9/16/2019 12:570:50
17674710C9/16/2019 13:480:33
17674710A9/16/2019 14:21167:19
17674710C9/23/2019 13:4064:39
17674710B9/26/2019 6:204:21
17674710C9/26/2019 10:42-316:40
17759183A9/25/2019 6:482:08
17759183C9/25/2019 8:57391:02
17759183A10/11/2019 15:5964:36
17759183C10/14/2019 8:3620:25
17759183B10/15/2019 5:011:46
17759183C10/15/2019 6:4780:54
17759183A10/18/2019 15:4264:14
17759183C10/21/2019 7:560:00

 

612 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-04-09 01:40 PM

In response to rathorep

May be this

Interval(Sum(Aggr(Interval(RangeMax(Below(TOTAL [Modified Date]) - [Modified Date], 0), 'hh.mm') , [Ticket Number], ([Modified Date], (NUMERIC)))), 'hh:mm')
608 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-04-09 01:46 PM

In response to sunny_talwar

or may be this

Interval(
  Sum(Aggr(
    
    Interval(Below([Modified Date]) - [Modified Date], 'hh.m')

  , [Ticket Number], ([Modified Date], (NUMERIC))))
, 'hh.mm')
1,788 Views
0 Likes