show only values with special content

[jakobjosef]

Hi guys, 

I think I need your help again, maybe someone is able to solve my problem. 

In my database, there are "tickets", and those "tickets" have "actions" - at least 1 action, but usually about 5-10. These actions have an "action-name" as well, for example "appointment arranged", "assign internal" or "closed".

I want to show all "tickets", wich are closed (so those which have an action-name "closed"). How can I show them?

I tried with "firstSortedValue", wich works fine for the majority of tickets, but sometimes "Closed" is not the last action of a ticket, e.g. when a closed ticket gets edited. 

Is there a function like:

" if there is a action-name "Closed", show Ticket-ID"?

if(action='Closed', Ticket-ID) doesnt work as well because usually there are several actions for one ticket. 

Hope you can help me. if there are any questions please do not hesitate to ask. 

Thank you
Jakob

[jakobjosef]

or maybe something like

"if exists('Close' in 'Action-name), ....

[pradosh_thakur]

TRY below

if(substringcount(UPPER(action),'CLOSED')>0,Ticket-ID)

[Marcos_rv]

Try this:

only( {$<action={'Closed'}>} Ticket-ID) 

This must be applied in the expression of a table, that is, everything that is in the graph (simple table or pivot) is filtered. You have to add dimensions.

you would have to add something else, for example dates in the case of having many states per ticket (the same ticket may be open or closed at different times, this depends on whether you are working with a photo of the database (latest data) or with a historical).

I hope this has helped you.

Regards!!!

[jakobjosef]

thanks guys, I''l try both and let you know 🙂

[jakobjosef]

hi marcos,
i think this would work - but I have one more question.

I thouhgt as soon I see the "closed Tickets" it would be easy to show the open ones, like mostly just using "<>" instead of "=".
But I think in this case it doesnt work, like:
"only( {$<action <> {'Closed'}>} Ticket-ID) "

Is there a way to see the opposite as well, so all Tickets which are "open"? I tried it with set analysis as well, like 1-$ or $-1, but I coulnd manage.

THank you so far!
Jakob