topic Re: Add "National" to dataset that only has Regions in App Development

Add "National" to dataset that only has Regions

Adfc_NZ — Thu, 16 Oct 2025 19:35:43 GMT

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.

Re: Add "National" to dataset that only has Regions

Vegar — Fri, 17 Oct 2025 04:57:54 GMT

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

Re: Add "National" to dataset that only has Regions

Vegar — Fri, 17 Oct 2025 05:00:17 GMT

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-level-scripting.htm

Re: Add "National" to dataset that only has Regions

hanna_choi — Fri, 17 Oct 2025 05:25:47 GMT

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;

Re: Add "National" to dataset that only has Regions

marcus_sommer — Fri, 17 Oct 2025 09:40:04 GMT

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

Re: Add "National" to dataset that only has Regions

Adfc_NZ — Tue, 28 Oct 2025 21:55:40 GMT

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

Re: Add "National" to dataset that only has Regions

hanna_choi — Wed, 29 Oct 2025 00:07:45 GMT

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.

Re: Add "National" to dataset that only has Regions

Adfc_NZ — Thu, 30 Oct 2025 07:52:33 GMT

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

Re: Add "National" to dataset that only has Regions

hanna_choi — Thu, 30 Oct 2025 07:59:03 GMT

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;

Re: Add "National" to dataset that only has Regions

Adfc_NZ — Thu, 30 Oct 2025 08:05:20 GMT

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!

Re: Add "National" to dataset that only has Regions

Adfc_NZ — Thu, 30 Oct 2025 08:29:19 GMT

I tried adding the following:

Let vRegionSelectedCount = GetSelectedCount(Region);

And then wrap the "Add Load" inside an if:

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

Add Load
'National' as Region, //Country is dimension Label.
$(vTotalSales) as 'Budget ($)' //Sales is measure Label.
Autogenerate 1;
end if

But this doesn't work:

What am I doing wrong? Or is this not even possible?

Re: Add "National" to dataset that only has Regions

hanna_choi — Fri, 31 Oct 2025 00:12:12 GMT

Hi @Adfc_NZ

I succeeded by changing the script as follows.

I have found that GetSelectedCount(Region) is not calculated.
So I set it as a variable to receive it as a constant value.

************

Let vRegionSelectedCount = $(vCountCountry);
Let P = HCNoRows();
If $(vRegionSelectedCount)= 0 then
For J = 1 to P
Let vRegionSelectedCount = vRegionSelectedCount + HCValue(#hc1.measure.1, J);
Next

Add Load
'National' as Country,
$(vRegionSelectedCount) as 'Budget ($)'
Autogenerate 1;
end if

**********************

***If I select Canada, the chart is display only canada and 'National' is disappear.

Re: Add "National" to dataset that only has Regions

Adfc_NZ — Fri, 31 Oct 2025 00:17:56 GMT

Hi @hanna_choi,

Great idea, calculate the SelectedCount outside and then pass it into the script, love it! 🙂

Thank you for your time, this solution will work brilliantly.

And thanks also to everyone who contributed with ideas! 🙂