How to display the correct number of IDs under a certain condition?

[Dmitryromanov23]

I have this problem. I have a table with the id of requests. Each request has a status. One request can be first given status "justified_warning" and later the same request can be given status "justified_repetition".

I need to display a text field that will count the number of requests by statuses for the selected period of time. For example, "justified - 5 requests, unjustifiably - 10 request, justified_warning - 10 requests".

The status justified consists of the sum of references with the statuses "justified_repetition" and "justified".

I need to make it so that when selecting a time range there is a count: If one request in this time range has both statuses ("justified_warning" and "justified_repetition"), then the request with the status "justified_warning" is not counted.

That is, in my example, when choosing February 2023, the result should be "justified - 5 requests, unjustifiably - 2 requests, justified_warning - 3 requests".
justified 5 = 4 justified + 1 justified_repetition
justified_warning 3 = 4 justified_warning - 1 (id 5 has "justified_repetition")

When selecting q1:
"justified - 8 requests, unjustifiably - 4 requests, justified_warning - 5 requests"

When selecting all data:
"justified - 10 requests, unjustifiably - 8 requests, justified_warning - 6 requests"

I only wrote code like this in the download to check if the ID has a "justified_warning" status and then has a "justified_repetition" status to get a new column with "0" for the "justified_repetition" status. And then work with that from there. But this is all on loading, and I need it to be done depending on time filters

LOAD
  id,
  status,
  If(status='justified_warning', id) as checkId,
  If(status='justified_repetition' and Exist('checkId',id)
    ,0 , 1) as new_column
From/resident...;

[Kushal_Chawda]

@Dmitryromanov23  try below

Data:
LOAD
    id,
    status,
    "date",
    Quartal
FROM [lib://Files/statuses.xlsx]
(ooxml, embedded labels, table is Лист1);


status:
Load * Inline [
pick_status
justified
unjustifiably
justified_warning];

Create a table with below dimension and measure

Dimension:
pick_status

Measure:
=pick(match(pick_status,'justified','unjustifiably','justified_warning'),
count(distinct {<status={'justified','justified_repetition'}>}id),
count(distinct {<status={'unjustifiably'}>}id),
count(distinct {<id= p({< status={'justified_warning'}>}id)*e({< status={'justified_repetition'}>}id) >}id)
)

[Dmitryromanov23]

Thanks a lot, Kushal! 

It works correctly. But can it be done in text field?

[Kushal_Chawda]

@Dmitryromanov23  I did not get. What is text field? Do you mean using the same status field in data?

[Dmitryromanov23]

I mean, is it possible to output information not in a table, but in a "text &image" box?

[Kushal_Chawda · Accepted solution]

@Dmitryromanov23  Yes. put below entire expression in text object

='justified : ' & count(distinct {<status={'justified','justified_repetition'}>}id) & chr(10) &
'unjustifiably : ' & count(distinct {<status={'unjustifiably'}>}id) & chr(10) &
'justified_warning : ' & count(distinct {<id= p({< status={'justified_warning'}>}id)*e({< status={'justified_repetition'}>}id) >}id)