Add "National" to dataset that only has Regions

[Adfc_NZ]

Hi,

I have a set of transactional data that uses a dim table with the regions. All good here, nothing special.

Issue is, the customer wants a barchart with an "extra" bar representing the "national", i.e. the totals.

I need your help and advise on this, because as I can see it, I have 2 options:

1- Summarise the data in the load script and concatenate it back to the fact with the "Region" being "National"

A few pros and cons, the major con imo is the fact that I'd then need to exclude "National" from all other areas of the app where "National" is not needed

2- Create a Dimension with the list of the Regions + "National", then in a measure in the chart to display each region + the national, use "Pick(Match(" along with 1 Set Analysis per region (that actually exist in the dataset) plus another where "Region=" then sums the amount.

This works very well, but the major con is that because of the Set Analysis the Region filter no longer works on this visual.

I am inclined to go with option 2, but just wondered if anyone here has a different solution to this requirement which might be more "flexible" than the 2 above?

Thank you.

[Vegar]

I suggest creating a dimension table for regions.

Let's assume you have this transaction table:

SalesData:

LOAD * INLINE [

id, region, sales_amount

1, North, 5500

2, South, 7200

3, East, 4100

4, West, 8900

];

Then you can create a table like this

RegionGrouping:
LOAD * INLINE [
region, RegionReportGroup
North, North
North, National
South, South
South, National
East, East
East, National
West, West
West, National
];

Then use only that report region where you need the both region and national, else you continue to use region

[Vegar]

Another, less known, method for this is to use chart level scripting. I think your case can be a very good fit for it.

See: https://help.qlik.com/en-US/sense/May2025/Subsystems/Hub/Content/Sense_Hub/ChartLevelScripting/chart...

[hanna_choi]

Hi @Adfc_NZ 

What do you think of using the Chart Level Script feature?
This feature is available for Aug 2022 and later versions.

***** Script 

Let vTotalSales = 0;
Let P = HCNoRows();
For J = 1 to P
Let vTotalSales = vTotalSales + HCValue(#hc1.measure.1, J);
Next

Add Load
'Total Sales' as Country,    //Country is dimension Label.
$(vTotalSales) as Sales    //Sales is measure Label.
Autogenerate 1;

[marcus_sommer]

Your second approach could in general be working without any conditional parts - the pick(match()) - or any set analysis. Just using the extra dimension from The As-Of Table - Qlik Community - 1466130 and sum(MyField).