Given the amount of cities I would recommend doing this in the script. I would create a new table with City and Group. you then join this table to the table where you city is stored.
Load * inline [
Chicago, Group 1
NY, Group 1
Miami, Group 2
Orlando, Group 2
you can then use your Group field in your chart.
In script using pick function divide these cities into 5 groups, give it name as Cities_Grouped.
In your chart: Dimensions->Edit Groups->New->Drill Down and select Cities_Grouped and Cities.
When you go to your chart you should be first presented with Cities_Grouped, clicking on any of the Cities_Grouped will then drill down to your 20 Cities in this group.
Dear Friend, would you please do it on a sample? i am not actually good at scripting.
I uploaded the sample.
I'd like to see what percentage of each city population lives in different temperature conditions.
the table has just 3 columns.
dimension: City, Temp
Type of chart: Stacked Bar Chart
QlickSample.xlsx 9.6 K
Check this file,
dril down with Temp->Cities
i.e once you click on any of the bar you will get citites having that temperature.
demo.qvw 149.0 K
I think your way would be the best one. Like that, you do not need to hardcode it in the script and if any new groups are going to be added you will add them into your database
You can always hardcoded in your script by using nested if statement or PICK() function will solve it too.
if(mixmatch(City,'New York'),'Group 3',
'Group 4'))) as City_Groups
'Group 1', 'Group 1','Group 2','Group 3')
as City Groups