Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to count the number of cases, in any current status, but only if they have ever been in a specific min(status).
Thanks in advance for any suggestions.
Fors
Would you be able to share few rows of data with the expected output from them?
If i have understood this correctly;
i think , you would just be able to do a simple set analysis, without any date modifiers and specifying what the specific status is as a modifier. a DISTINCT of the count should give you the number of unique cases, that has ever had a the specific status.
Count({<Status={'$(specific_status)'}>}DISTINCT CASES)
Thanks, Aun!
Using your example, I did this:
Count({<min(NEW_CMPLN_STATUS_CDE)={'$('30')'} DISTINCT CMPLN_ID)
but it's returning the "-"
So Basicaly...
Case#, Curr_Status, Status_History
(includes)
1 90 10,15,20,30,36,90
2 90 10,20,30,42,90
3 90 10,15,20,90
4 30 10,30
90 = Closed
Attempting to return all in Status 90, but only if they have ever been in status 30.
I would expect to return only Case # 1, and 2 (since 3 was never in status 30, and obviously 4 is not in 90.)
May be like this
Sum({<Curr_Status = {'90'}, Status_History = {"*30*"}>}Measure)
or this if Status_History is not really a column
Sum({<Curr_Status = {'90'}, Case# = p({<Status = {'30'}>})>} Measure)
Try
Sum(If(SubStringCount(Status_History,'90') and SubStringCount(Status_History,'30'),1,0))