-
Re: Qlik Sense: Convert CountIF to Set Analysis
Sunny Talwar Jul 27, 2017 3:06 PM (in response to Steven Demny)Do you have a field which uniquely define the combination of ClientReportingDate and ClaimDt? May be like a ClaimID or something like this?
-
Re: Qlik Sense: Convert CountIF to Set Analysis
Steven Demny Jul 27, 2017 3:36 PM (in response to Sunny Talwar )Yes, they are linked by MemberID
-
Re: Qlik Sense: Convert CountIF to Set Analysis
Sunny Talwar Jul 27, 2017 4:20 PM (in response to Steven Demny)Try this
Count({$<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist) / Count({1<MemberID = {"=ClientReportingDate >= ClaimDt"}>}Specialist)
-
Re: Qlik Sense: Convert CountIF to Set Analysis
Ruben Marin Jul 27, 2017 4:33 PM (in response to Steven Demny)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)
-
-
-
Re: Qlik Sense: Convert CountIF to Set Analysis
Ruben Marin Jul 27, 2017 3:55 PM (in response to Steven Demny)*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)
-
Re: Qlik Sense: Convert CountIF to Set Analysis
Steven Demny Jul 27, 2017 4:48 PM (in response to Ruben Marin )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.
-
-
Re: Qlik Sense: Convert CountIF to Set Analysis
Srikanth P Jul 27, 2017 9:25 PM (in response to Steven Demny)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.