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).

[Adfc_NZ]

Thank you everyone for your replies, all very interesting!

But I neglected to say something very important, this is re Qlik Cloud, where I cannot see the "Chart Scripting" option.

I am going to try Vegar's suggestion to use a dim table for regions.

Thank you again to everyone. 🙂

[hanna_choi]

Hi @Adfc_NZ 

The chart script function must be enabled in the app settings.
Once enabled , you can check the chart script menu in the chart.

[Adfc_NZ]

Hi Hanna_choi,

Thank you for showing me something new, there's always something to learn isn't there?! 😄

Now, from the sample script you posted, I am assuming I need to replace "#hc1.measure.1" with my measure, am I correct? What else do I need to adjust to "match" my visual/dimensions/measures?

As I've never user Chart Scripts I am flying completely blind here, sorry...

And again, thank you. 🙂

[hanna_choi · Accepted solution]

Hello @Adfc_NZ 

You need to configure the red part to be the same as the label of the measure and dimension.

The inquired '#hc1.measure.1' is a function that utilizes the measurements defined in the graph.

***** 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;

[Adfc_NZ]

Hi Hanna_choi,

Amazing!! Thank you so much, that did the trick.

But, and risking sounding ungrateful for all the help so far (hope not), there's only one issue: obviously this  new dimension value/column won't respond to "Region" filtering (it doesn't "go away" :D) as it isn't a "real region". Is there any function that could be used somehow, to check if the filter is being used to make the "National" column disappear?

Thank you again!