Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
May be this
Max({<Zip>} Aggr(Count(DISTINCT {<Zip, Numerator={'1'}>} Key)/Count(DISTINCT {<Zip, Denominator={'1'}>} Key), County, Zip))
@sunny_talwar any ideas on this one? You've been a great help with these types of questions! Thanks so much!
maybe this expression gives you the desired value
max(aggr(Count(Distinct {<Numerator={'1'}>} Key)/Count(Distinct {<Denominator={'1'}>} Key),Zip))
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?
@Anonymousany ideas to resolve the above issue? Thanks so much for your help!
May be this
Max({<Zip>} Aggr(Count(DISTINCT {<Zip, Numerator={'1'}>} Key)/Count(DISTINCT {<Zip, Denominator={'1'}>} Key), County, Zip))
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))
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))
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!
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))