How to show only one value in a dimension

[BigSky5829]

Hi, let's say I have data that looks like this:

Ticket ID	Minutes	Ticket Closed	Status
123456	5	N	Ongoing
123456	7	N	Ongoing
123456	1	Y	Resolved

I'm using the UI to try to build a table like this:

Ticket ID	Minutes	Status
123456	13	Resolved

What I am struggling with is how to get the Status.  I'm using this formula: =if([Ticket Closed]='Y',[Status])

Result is this:

Ticket ID	Minutes	Status
123456	1	Resolved
123456	12	-

I think I know why my formula is producing those results.  I just don't know what formula I should be using.

[QFabian · Accepted solution]

Hi @BigSky5829 , please check if this works for you :

example script :

Load * INLINE [
Ticket ID, Minutes, Ticket Closed, Status
123456, 5, N, Ongoing
123456, 7, N, Ongoing
123456, 1, Y, Resolved
123457, 7, N, Ongoing
123458, 1, N, Ongoing
123458, 1, N, Ongoing
123458, 1, Y, Resolved
];

table chart :

chart expression :

if(SubStringCount(Concat([Ticket Closed], '|'), 'Y'), Sum(Minutes))

[QFabian]

to not show Ticket ID that are not Closed, uncheck this

[BigSky5829]

Not quite.  The totals line is adding up correctly but the individual row isn't right.  I also need the status column to be shown, I don't see that in your example.  I'm going to continue playing with this to see if I can get it.

[QFabian]

Add this expression column :

if(SubStringCount(Concat([Ticket Closed], '|'), 'Y'), 'Resolved')

[BigSky5829]

It might not always be 'resolved', it could be 'did not replicate' or 'no response from client' or something like that.  I think I might be able to figure it out with the substrings, I'll race you.

[BigSky5829 · Accepted solution]

I think I got it with this!

=SUBFIELD(if(SubStringCount(Concat([Ticket Closed], '|'), 'Y'), Concat([Status], '|',IF([Ticket Closed]='N',0,1))),'|',-1)

Thank you for your help!

[QFabian]

excellent! @BigSky5829