Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Data looks as below, A Ticket is tagged to multiple Types .
Ticket ID | Type |
A | Very High |
A | High |
A | Low |
B | High |
B | Medium |
C | High |
A Ticket count has to be calculated only in one bucket. i.e., if a Ticket is tagged to 'Very High', 'High' or 'Low' , it's count should be shown only in 'Very High' . Similarly if a ticket is tagged to High and Low, its count should be reflected only in High Bucket.
Current State
Very High | High | Medium | Low |
1 | 3 | 1 | 0 |
Expected State
Very High | High | Medium | Low |
1 | 2 | 0 | 0 |
Please let me know if you need further details.
Thanks,
I like solving these kind of problem by assigning a numeric value to the text (Type)
Script
Table:
LOAD *,
Match(Type, 'Very High', 'High', 'Medium', 'Low') as TypeNum;
LOAD * INLINE [
Ticket ID, Type
A, Very High
A, High
A, Low
B, High
B, Medium
C, High
];
In your chart
Dimension
Type
Expression
=Sum(Aggr(If(TypeNum = Min(TOTAL <[Ticket ID]> TypeNum), 1), [Ticket ID], TypeNum))