Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
scienceandmatht
Contributor
Contributor

Qlik Sense: Convert CountIF to Set Analysis

Hi Community,

I have a Count IF statement that tells me the percentage of Specialist visits that happen before a reporting date.  It works fine.

Count(If(ClientReportingDate>=ClaimDt, Specialist)) / Count(Total(If(ClientReportingDate>=ClaimDt, Specialist)))

I am trying to convert it to Set Analysis to increase app performance.  Here is the equation I've tried, but it doesn't work.

Count({$<ClientReportingDate={'>=ClaimDt'}>}Specialist) / Count({1<ClientReportingDate={'>=ClaimDt'}>}Specialist)

When just looking at the numerator, it doesn't work.

Count({$<ClientReportingDate={'>=ClaimDt'}>}Specialist)


I looked through a lot of forums but can't seem to find what works in Qlik Sense.  What am I missing?  I appreciate your help!


-SD

7 Replies
sunny_talwar

Do you have a field which uniquely define the combination of ClientReportingDate and ClaimDt? May be like a ClaimID or something like this?

scienceandmatht
Contributor
Contributor
Author

Yes, they are linked by MemberID

rubenmarin

*Edited*

Hi Steven, if there are more than one possible value for ClaimDt it will retun a Null() value, you need an aggregate function to retun only one value, ie:

Count({$<ClientReportingDate={">=$(=Min(ClaimDt))"}>}Specialist)


If ClientReportingDate and ClaimDt are values in the same record your best option is flag this records on script (or a calculated field in data manager):

If(ClientReportingDate>=ClaimDt, 1, 0) as FieldName


Then use this field on set analysis:

Count({$<FieldName={1}>}Specialist)

sunny_talwar

Try this

Count({$<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist) / Count({1<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist)

rubenmarin

Your original expression has a TOTAL, which was changed to a '1' in set analysis, maybe you want? (copied from Sunny's post):

Count({$<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist) / Count(TOTAL {$<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist)

scienceandmatht
Contributor
Contributor
Author

Creating a flag and doing a preceding load was an option we were playing with, we'll probably have to revisit the idea.  Your previous post was giving results of 100% for every Specialist category.  Will revisit the load statement.  Thanks for your help, I'll post the successful results once completed.

Not applicable

Steven, SET Analysis won't work on row by row execution. It takes your filters as the whole SET and generates the results with the scope of Dimensions in the chart.

The below Ruben mentioned expression should work you have a proper association between the tables in your data model:

Count({$<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist) / Count(TOTAL{$<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist)

Adding the flag in the script is a better approach.