Count with group on 2nd column

[rathorep]

Hello,

I did find some solution to similar question but unfortunately, non of them gave me desired result.

Below is my question, I have a table with ticket number and group name who modified the ticket.

I have to calculate, how many times group has modified each ticket.

Ticket No	Group Name
17698506	A
17698506	B
17698506	A
17698506	C
17698566	A
17698566	B
17698566	B
Required result
Ticket No	Group Name	Count of Group name for each ticket
17698506	A	2
17698506	B	1
17698506	C	1
17698566	A	1
17698566	B	2

 Thanks!!!!

[Taoufiq_Zarra · Accepted solution]

@rathorep  like ?

Data:
LOAD * INLINE [
    Ticket No, Group Name
    17698506, A
    17698506, B
    17698506, A
    17698506, C
    17698566, A
    17698566, B
    17698566, B
];

output:

load [Ticket No], [Group Name],count([Ticket No]) as [Count of Group name for each ticket] resident Data group by [Ticket No], [Group Name];

drop table Data;

output:

[Kushal_Chawda]

@rathorep  create a table with Dimension Ticket No & Group Name with expression count(Group_Name)

[rathorep]

I am fetching data from QVD and i have to do this directly in chart.

[rathorep]

Hello @Kushal_Chawda , it will give big count for each group and on putting disctinct , its only giving 1 for each group.

[Taoufiq_Zarra]

@rathorep 

chart

dimesntion : ticket No and Group name

measure count(Ticket No)

like :

[Kushal_Chawda · Accepted solution]

@rathorep  look at below. based on your example I have created below

[rathorep]

Hello,

when I am doing same as you suggested on dummy data , I am getting correct result.

But when I am doing same in new sheet of my APP, its giving wrong result, like for this ticket count is getting multiplied by 6 , and for other records as well its getting multiplied by 2 or 5 or some random number. I am not sure how the count is getting multiplied.

Ticket No	Group Name	Count of Group name for each ticket		Result I am getting
17698506	A	2	*6	12
17698506	B	1	*6	6
17698506	C	1	*6	6
17698566	A	1	*6	6
17698566	B	2	*6	12

[rathorep]

Is there any other way we can do this calculation?