I had earlier built this viz using an SQL query that gets the top 7 area s that have the highest count. In this query, I would ONLY return the top 7 areas of a particular column.
I would write a separate CTE that would count the most common areas and return those areas in descending order and put a limit of 7.
Now, I want this to be a top n value where n can be 1, 2, 3, ..., n such that the chart will breakdown and show the travel_time for n areas.
How do I do this?
My initial plan was to write a query the same way, however I would not be able to vary the n value from the dashboard without going to the query. I know that I can make a field filter but I am struggling to do so.
This is how the aforementioned CTE looks like:
top_7_area AS (
SELECT Area
FROM main_table
WHERE
ISNULL(Area,'') <> ' ' AND -- some area values are blank
Area IS NOT NULL
GROUP BY Area
ORDER BY COUNT(*) DESC
LIMIT 7
)
Hey, can you try adding a SQL variable like {{top_n_value}} in your LIMIT clause? You will be able to attach the SQL variable to a filter for the "Top N value".
Make sure to mark the filter "Required" so that the SQL query will run.
Might look like this:
top_7_area AS (
SELECT Area
FROM main_table
WHERE
ISNULL(Area,'') <> ' ' AND -- some area values are blank
Area IS NOT NULL
GROUP BY Area
ORDER BY COUNT(*) DESC
LIMIT {{top_n_value}}
)
WHERE
home_area IN (SELECT * FROM top_n_home_area)
AND office_area IN (SELECT * FROM top_n_office_area)
It is saying "get ALL the rows in merged_results where the home_area is either home area 1, 2, 3, 4, or 5". So that's why you see the same values repeated in your results.
I recommend building a table that looks like this instead. This will let you select different N values for "home" and "office" too.
area_name
area_type
area_1
home
area_2
home
area_3
home
area_4
home
area_5
home
area_6
office
area_7
office
area_8
office
area_9
office
area_10
office
Your last SELECT statement will look a bit like this:
SELECT
area_name,
'home' AS area_type,
average_travel_time
FROM top_n_home_area
UNION
SELECT
area_name,
'office' AS area_type
FROM top_n_office_area
