Get count from multiple results.

[#1]

Hi guys ive got a problem, i think its simple but I cant seem to get it working, here is the problem.

I have several Tickets lets say A, B, C, D, and during time the tickets change status so Ticket A has been status 1, 2 and 3, ticket B has been status 2 and 3, Ticket C haben been status 1 and 2 and Ticket D has been status 1 and 3.

Like so;

Ticket	Status
A	1
A	2
A	3
B	2
B	3
C	1
C	2
D	1
D	3

So I want a calculation that counts the number of tickets that have ever been status 1 and the number of tickets that have had any status expect 1.

SO in this case the result would be.

Status 1 Tickets = 3 (A, C, D)

Status non 1 Tickets = 1 (B)

Thanks

[syukyo_zhu · Accepted solution]

Status 1 Tickets = 3 (A, C, D) :  Count(distinct {<Ticket=p({<Status={1}>}Ticket)>}Ticket)

Status non 1 Tickets = 1 (B): Count(distinct {<Ticket=e({<Status={1}>}Ticket)>}Ticket)

[ali_hijazi]

I would load your data

then get the min(Status) by Ticket

temp:

Load Ticket, min(Status) as Status

resident your_table

group by Ticket;

temp2:

load * if(status=1, 1,0) as Got_Status_1

resident temp;

drop table temp;

inner join(your_table)

load Ticket, Got_status_1 resident temp2;

drop table temp2;

your expression will be

count(distinct {<Got_Status_1={1}>} Ticket) for those who got 1 at least once

the second would be count(distinct {<Got_Status_1={0}>} Ticket)

[giacomom]

Hi Gualter,

you can use set analysis expressions, both to:

1. count the number of tickets that have been status 1:

     =Count({$ <Status={1}>} DISTINCT Ticket)

2. count the number of tickets that have been any status but 1:

     =Aggr(Count({$ <Status=-{1}>} DISTINCT Ticket), Ticket)

Regards,

Giacomo

[#5]

Sorry I should have made a better example, my Status are actual Strings and not numbers, so I cant use Min or Max.

[PradeepReddy]

In front end using set analysis...

1) Status 1 Tickets = 3 (A, C, D)

   count(distinct {<Status={'1'}>}Ticket)

2) Status non 1 Tickets = 1 (B)

count(distinct Ticket) - count(distinct {<Status={'1'}>}Ticket)

[jerrysvensson]

Have you looked at syukyo_ZHU solution(further up)?

Should work perfectly and it is fast.

[ali_hijazi]

you can use minstring