topic Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions in App Development

KPI Value - Count of all FamilyIDs that satisfy multiple conditions

Wed, 22 Dec 2021 15:42:54 GMT

Hi,

I've been using QlikSense for a month now. I'm trying to build a KPI which is a division of 2 counts. The numerator is slightly complicated- the count of all distinct family IDs whose total sum of Field1 and Field2 is greater than or equal to 50k, who belong to customer 'A' and have been Assisted. The denominator is the count of all families that belong to customer 'A' and whose total sum of Field1 and Field 2 is greater than or equal to 50k. I've tried a few things but doesn't seem to work. The code I wrote is as follows:

count({<NEW_FAMILYID = {"=sum(Field1)+sum(Field2)>=50000"}, CUSTOMER={'A'}, ASSISTED_CLIENT={'1'} >} distinct NEW_FAMILYID)

count({<NEW_FAMILYID = {"=sum(Field1)+sum(Field2)>=50000"}, CUSTOMER={'A'}>} distinct NEW_FAMILYID)

Another thing I tried:

count({<ASSISTED_CLIENT = {'1'},

CUSTOMER = {'A'}>}

distinct If(Aggr(sum(Field1) + Sum(Field2),NEW_FAMILYID)>='50000', NEW_FAMILYID))

COUNT({<CUSTOMER = {'A}>} DISTINCT If(Aggr(sum(Field1) + Sum(Field2),NEW_FAMILYID)>='50000', NEW_FAMILYID))

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

sunny_talwar — Fri, 21 Jul 2017 18:34:44 GMT

When you say it isn't working, what is the issue? Incorrect result or error? May be try this

Count({<NEW_FAMILYID = {"=Sum({<CUSTOMER={'A'}, ASSISTED_CLIENT={'1'}>}Field1)+Sum({<CUSTOMER={'A'}, ASSISTED_CLIENT={'1'}>}Field2)>=50000"}, CUSTOMER={'A'}, ASSISTED_CLIENT={'1'}>} DISTINCT NEW_FAMILYID)

Count({<NEW_FAMILYID = {"Sum({<CUSTOMER={'A'}>}Field1)+Sum({<CUSTOMER={'A'}>}Field2)>=50000"}, CUSTOMER={'A'}>} distinct NEW_FAMILYID)

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

Fri, 21 Jul 2017 19:31:32 GMT

Hi Sunny,

Previously, I used my formula to come up with a line chart where I tracked the KPI value across months. When I used the original formula that I posted about, I was getting all the data points on the chart. However, the data point values were wrong.

Say for January 2017, the actual value is 43.75%. On the chart, I can see something like 34%.

When I drill down using the Month and Year filter and select Jan 2017, the chart value then changes to 43.75%.

But in the chart where it is tracked through out the year, the value displayed is wrong.

When I used your formula, the chart just blanks out. Nothing is displayed across the chart. The screenshot is as follows:

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

sunny_talwar — Fri, 21 Jul 2017 19:38:23 GMT

Would you be able to share your qvf file to look at?

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

Mon, 24 Jul 2017 16:26:46 GMT

Unfortunately not.

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

mjtaft2017 — Mon, 24 Jul 2017 18:48:48 GMT

Have you tried creating additional fields in your load script and then using those? If that is possible, it has made a difference with some of my visualizations to put the complicated logic in the load to create additional fields or variables and then use those in my charts.

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

sunny_talwar — Mon, 24 Jul 2017 23:48:27 GMT

Mock up some data?

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

Anonymous — Wed, 26 Jul 2017 11:33:10 GMT

Hi ,

Try with following expression :

Count({<NEW_FAMILYID = {"Sum({<CUSTOMER={'A'}>}Field1)+Sum({<CUSTOMER={'A'}>}Field2)>=50000"}, CUSTOMER={'A'}>} distinct NEW_FAMILYID)

Re: KPI Value - Count of all FamilyIDs that satisfy multiple conditions

Wed, 26 Jul 2017 18:13:14 GMT

This is giving the same output as Sunny's. The entire chart just blanks out.