Hello all,
I've been trying to piece together some custom queries, and I might have stumbled upon success
I'm looking for a way to write a formula that says "Show me the top 10 (by Attended Count) geo areas, BUT NOT [X Geo Area]"? The 'top 10 by attended' is straightforward (once I set up the ranking), and I can also do the 'exclude [X Geo Area]' bit. When I stitch them together (copy-pasting what it auto-generates for a formula), this is what I get:
"{"and": [{ "top": 10, "by": { "type": "measure", "formula": "sum([10D84-BFF])", "context": { "[10D84-BFF]": { "table": "SEAT & TICKET VALUES", "column": "Attended Count", "dim": "[SEAT & TICKET VALUES.Attended Count]", "datatype": "numeric", "title": "Attended Count" } }, "title": "Attended Count" }},
{ "explicit": false, "multiSelection": true, "exclude": { "members": [ "Denton County, TX" ] }}],"custom": true}
"
Analytics accepts this, so it seems correct, but I would love some verification, or ways to make this easier. Basically, I wanted to remove one geo_area visually (we use Kiosks, and they all report into one constituent, so the geographic data is skewed), but not twist the data for the whole dashboard. Does that make sense? Is there a cleaner way forward, rather than stitching together two separate queries? If I wanted to use this concept (top X number, BUT NOT [X member], is this the way forward?
Hi Nathanael,
You have the right idea for using AND or OR with a custom filter. This is a really useful tool to have in your toolkit. It is also possible in this case to get what you're after without the coding challenges of ANDing in this way.
We can instead create a dashboard filter on Geo Area and select the Ranking type filter for TOP 10, and use a formula such as this where for each Geo Area in the ranking, if the Total Attended Count of the Geo Area is equal to the Total Attended Count of the [X Geo Area], then return a rank of NULL, otherwise return Total Attended Count as the rank.
MAX( IF ( [Total Attended Count] = ( [Total Attended Count] , [Primary Address Geo Area = [X Geo Area]] ) , NULL , [Total Attended Count] ))
I used [# unique Constituent ID] in my testing:
The risk here is that 2 Geo Areas have the same Total Attended Count, in which case a valid one might be excluded.
Best,Chris