Count distinct but SUCCESS clients. Part 2

[AlexWest]

Hi guys!

Yesterday I've made a post about distinct count and I've found an answer if there is 1 condition.

But now I have the same question but there are 2 conditions. And I tried everything and couldn't solve the task.

Well, I have an Excel table

The task is to get only those IDs that was Failed and never was SUCCESS and have a Fail_Reason.

I tried to count with E function, but there was only 1 condition. Every try to count with 2 conditions I got wrong numbers.

If you can, please help))

Thanks a lot!

[rubenmarin]

Hi, it's not clear when the "have a Fail_Reason" has to be applied, but it could be something like:

- Count(DISTINCT {<[Client ID]=E({<reg_Status={'Success'}>})*P({<Fail_reason={"*"}>})>} [Client ID])

Or: Count(DISTINCT {<[Client ID]=E({<reg_Status={'Success'}>})*P({<reg_Status={'Failed'},Fail_reason={"*"}>})>} [Client ID])

E() gets the excluded, P() the possible and the '*' between them picks the [Client ID] that has both conditions.

[Mark_Little]

HI @Alex

I would be temped to create a flag in the script to check for this making the set analysis much simpler,.

Something like

Left Join

Load

[Client ID],

IF( Count({<reg_status={'Success'}>}[Client ID]) = 0 And Count(Failed) = 0, 1,0) AS Flag

Resident...

[AlexWest]

It counts only all raws Clients who never got a SUCCESS in reg_Status, and this is what I needed too.

But now I need to count among Clients who never got a SUCCESS in reg_Status or among Failed only Clients with Fail Reason not NULL

[AlexWest]

Unfortunately,  {<reg_status={'Success'}>} this function doesn't work in Load Editor(

[rubenmarin]

Hi, it doesn't counts clients with success, the only diffence between 2 options is that the second counts the ones withouth success and with any failed with reason, and the first option only looks for reason failed, doesn't needs the status to be Failed, but still doesn't counts any client with sucess.

[AlexWest]

Yeah, I understand that the formula doesn't count Clients with SUCCESS status, but when I try to count with this formula, I have result only those clients, who never got SUCCESS but Fail_Reason doesn't  change.

For example, I have 8000 clients,

and with formula 

- Count(DISTINCT {<[Client ID]=E({<reg_Status={'Success'}>})*P({<Fail_reason={"*"}>})>} [Client ID])

I have -3000

Or with formula

Count(DISTINCT {<[Client ID]=E({<reg_Status={'Success'}>})*P({<reg_Status={'Failed'},Fail_reason={"*"}>})>} [Client ID])

I have +3000.

And if in Excel I sort by reg_Status, then remove Client dubles and make a filter "without SUCCESS", I have exactly 3000 clients, including empty and not empty Fail_Reason records.

So, maybe I'm doing something wrong?

[rubenmarin]

I tested with this dummy data:

- Option 1 counts ClientID with no sucess and reason.

- Option 2 counts ClientId with no sucess and Failed with reason.

Can you upload some sample data to tests, it will be better if you upload that incorrect counts.

[AlexWest]

Sure, I attached my original Excel file with original data changed, but all raws are there.

My task:----------------------------------------------

If you check the second Sheet 'Filtered for checking', I sorted a reg_Status by Z-A for SUCCESS result up.

Then I removed Client ID doubbles.

Now I have DISTINCT Clients who was SUCCESSED and other, who FAILED.

And among these FAILED Clients I have to get those who have Fail_Reason and those who haven't.

End of task:----------------------------------------------

But in Qlik I have to work only with Original Sheet.

P.S. in original Sheet I added new field where I changed empty Fail_Status on 'None', the same move I made in Qlik to use your formulas (include/exclude data), because {<...>} this func doesn't work with NULL()

[rubenmarin]

It looks fine to me, the only change is that Success is all uppercase in the data so the set analysis has to be changed to be uppercase.

In this sample it only dentifies 32 and 24 as the client id that meets the requirements

If you found any error post that particular Client ID that is wrong.