Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kmstephenson
Creator
Creator

Highest Rate of a ZIP in County - How to add to Chart

Hi All,

I have a chart in which I would like to display a rate for a selected ZIP and county, and the highest rate ZIP in the county selected. See below mock-up:

kmstephenson_0-1593007419098.png

My data is structured in a way that I can use the following expressions for Zip and County (assuming Zip and County are both required selections):

Zip rate bar:

Count(Distinct {<Numerator={'1'}>} Key)/Count(Distinct {<Denominator={'1'}>} Key)

County rate bar:

Count(Distinct {<Zip,Numerator={'1'}>} Key)/Count(Distinct {<Zip,Denominator={'1'}>} Key)

 

What logic would I need to add the green bar above - the Zip in the selected county with the highest rate? Currently I am not using a dimension to get the other two bars to appear.

1 Solution

Accepted Solutions
sunny_talwar

May be this

Max({<Zip>} Aggr(Count(DISTINCT {<Zip, Numerator={'1'}>} Key)/Count(DISTINCT {<Zip, Denominator={'1'}>} Key), County, Zip))

View solution in original post

9 Replies
kmstephenson
Creator
Creator
Author

@sunny_talwar any ideas on this one? You've been a great help with these types of questions! Thanks so much!

Anonymous
Not applicable

maybe this expression gives you the desired value

max(aggr(Count(Distinct {<Numerator={'1'}>} Key)/Count(Distinct {<Denominator={'1'}>} Key),Zip))

kmstephenson
Creator
Creator
Author

This works only when County is selected but Zip is not selected. However, both Zip and County will be selected. When Zip is selected, this expression shows the value of the selected Zip, not the highest Zip in the county (regardless of selections). Any thoughts on modifying this to work when a Zip is selected?

kmstephenson
Creator
Creator
Author

@Anonymousany ideas to resolve the above issue? Thanks so much for your help!

sunny_talwar

May be this

Max({<Zip>} Aggr(Count(DISTINCT {<Zip, Numerator={'1'}>} Key)/Count(DISTINCT {<Zip, Denominator={'1'}>} Key), County, Zip))
kmstephenson
Creator
Creator
Author

Thank you, @Sunny ! This was what I was looking for. A request just came in though to only consider ZIPs meeting a certain threshold (for example, a denominator >=100):

Count(Distinct {<Denominator={'1'}>} Key)>100

How would you add this to your expression to limit which ZIPs are being considered within the county? I tried to add the following IF() but it doesn't appear to work when a ZIP is selected. Thoughts on how to modify this? Thank you!!

Max({<Zip>} Aggr(IF(Count(Distinct {<Denominator={'1'}>} Key)>100,Count(DISTINCT {<Zip, Numerator={'1'}>} Key)/Count(DISTINCT {<Zip, Denominator={'1'}>} Key)), County, Zip))

sunny_talwar

May be this

Max({<Zip>} Aggr(Count(DISTINCT {<Zip = {"=Count(DISTINCT {<Zip, Denominator={'1'}>} Key) > 100"}, Numerator={'1'}>} Key)/Count(DISTINCT {<Zip = {"=Count(DISTINCT {<Zip, Denominator={'1'}>} Key) > 100"}, Denominator={'1'}>} Key), County, Zip))
kmstephenson
Creator
Creator
Author

This worked great, @sunny_talwar ! One more request this morning - if I wanted to add another requirement for which ZIPs are considered, how do I add this to the set analysis? In addition to the denominator >= 100, I have now also been requested to limit to only ZIPs with a data coverage of 10%+:

COUNT(DISTINCT {<Denominator={'1'}>} Key)/SUM(PopZipCnt) >=.10

How do I add this to the set analysis below to require this coverage threshold in addition to the denominator threshold already included? 

Thank you so much!

 

sunny_talwar

May be this

Max({<Zip>} Aggr(Count(DISTINCT {<Zip = {"=Count(DISTINCT {<Zip, Denominator={'1'}>} Key) > 100 and Count(DISTINCT {<Denominator={'1'}>} Key)/SUM(PopZipCnt) >=.10"}, Numerator={'1'}>} Key)/Count(DISTINCT {<Zip = {"=Count(DISTINCT {<Zip, Denominator={'1'}>} Key) > 100 and Count(DISTINCT {<Denominator={'1'}>} Key)/SUM(PopZipCnt) >=.10"}, Denominator={'1'}>} Key), County, Zip))