Chart Level Scripting - Use Cases, Samples and Examples

[Michael_Tarallo]

I love this new capability - Chart Level Scripting! This takes generating insights to a whole new level. No longer am I required to write complex reporting frameworks or move complex logic to the backend data model. Read on!

[Michael_Tarallo]

Hi Guys - I'd like to introduce you to some use cases, samples and examples of Qlik Sense Chart level scripting courtesy of Qlik Solution Architect, Venkat Subharaman. You can learn more about Venkat at then end of this blog. 

Recently we released a new capability in Qlik Sense, that allows you to provide and manipulate data directly within a chart object using script. It allows users to modify the data set behind a chart using a subset of the Qlik scripting language, using techniques such as variables and loops. Rows and columns can be added or modified that were not in the original data set enabling calculations that have previously not been possible in chart expressions or with variables.  Offering a new way to analyze your data such as simulations and goal seeking.

Venkat was so excited when he developed a few use cases for Qlik Sense Chart level scripting and worked out a few examples he wanted to share with everyone that might need them. (attached to this post)

Note that official documentation is available on the Qlik Help site and additional examples will be made available so stay tuned. 

I'd like to thank Venkat for his valuable contribution. Take it away Venkat!

With Chart level scripting I can create simple data models and write complex logics on a subset of data on the fly. Here are some of the benefits:

1. Customization
2. Flexibility
3. Maintainability

I have found it especially useful where working with financial reports when performing the following:

1. Running totals (Sounds so simple)
2. I have more complex % to be calculated based on business rules
3. I want calculation on the current row to be based on the rows above or below.

Traditionally I can use out-of-the-box functions like RangeSum and achieve the desired result. BUT, depending on the business use case and the requirements, formulas can get complex very quickly. Even though our we achieved the desired result, it is always at the back of my mind how will it be maintained going forward if something changes. 

Chart Level Scripting gives you a simple way of doing complex calculation with the added ease of working with just a small subset of data that is utilized in the chart or table.

Let's take a look at 3 simple examples that will highlight only a small percentage of the power of Chart level scripting.

First I want to just set the stage and familiarize you with some terminology and functions.

• HCNoRows(): returns the number of rows in the chart or table
• HCValue : Function that is used to return the value in a row for a dimension or measure
• HC1: Prefix used to represent the subset of data from the Hyper Cube that is utilized in the current chart or table.
• HC1.Measure.<MeasureNo> : Represents the measure in the chart or table
• HC1.dimension.<dimensionNo> : Represents the dimension in the chart or table

Example 1: Running total and percentage calculated at aggregate level

Steps:

1. Assumption : Enabled Chart Level Scripting
2. I have created a straight table with
   1. Dimensions: Division and Financial Month
   2. Measure: Actuals (Sum(Actuals))
3. Create 2 additional dummy measures
   1. Additional Measure 1: Give Sum(0) as the Expression and Running Total as the label
   2. Additional Measure 2: Give Sum(0) as the Expression and % as the label
4. Copy the script from the attached Example1.txt in the attached .zip file.

Add the division filter and select the required division, you will see that the totals and % will be re calculated and will adapt as per the selections. In the above example we just created running totals and percentage at the total level. Let us look at example 2 where we will do the calculation at the aggregate level as well as at the divisional level.

Example 2: Running total and percentage calculations at the aggregate level and at the divisional level

Steps:

1. Assumption : Enabled Chart Level Scripting
2. I have created a straight table with
   1. Dimensions: Division and Financial Month
   2. Measure: Actuals
3. Create 2 additional dummy measures
   1. Additional Measure 1: Give Sum(0) as the Expression and Running Total as the label
   2. Additional Measure 2: Give Sum(0) as the Expression and % as the label
   3. Additional Measure 3: Give Sum(0) as the Expression and Divisional Total as the label
   4. Additional Measure 4: Give Sum(0) as the Expression and Divisional % as the label
4. Copy the script from the attached Example2.txt in the attached .zip file.

Now the totals will be calculated at the aggregate level and at the Divisional Level.

Please note that I have just written the script in a simple fashion to showcase the functionalities like

1. Control statements
2. Regular statements
3. Prefixes (We will look at this in example 3)

Example 3: Aggregated measures and dimensions in a bar graph

Steps:

1. Assumption : Enabled Chart Level Scripting
2. Drag and drop a bar graph on a sheet and
   1. Dimensions: Financial Month
   2. Measure: Actuals
3. Copy the script from the attached Example3.txt in the attached .zip file.

The above 3 samples are simple uses cases to showcase the power of chart level scripting. Share with me your use cases  where you can apply Chart level scripting .

In summary Chart level scripting provides a simpler and compact way of solving specific business problems.

Happy Qliking!!!!!!!!!

Useful References

1. Turning on chart level scripting ‒ Qlik Cloud
2. Using chart level scripting and Limitations of Chart Level Scripting ‒ Qlik Cloud
3. Chart level scripting ‒ Qlik Cloud

1. Control statements ‒ Qlik Cloud
2. Prefixes ‒ Qlik Cloud
3. Regular statements ‒ Qlik Cloud 

About Venkat

Venkat is a Solution Architect, currently working with the presales team at Melbourne (Australia), having a broad experience in leading and systematizing BI Initiatives. With 25+ years of experience. Venkat has participated in various development projects & has played a key development role in the Enterprise Level Data warehouse & Business Intelligence Applications. Venkat has a deep interest in research and data analysis, drawing actionable insights from the raw data to help further the business’ goal. 

www.linkedin.com/in/venkitasubharaman

chart_level_scripting_samples.zip

[alex_nerush]

Nice examples. Is it possible to modify Totals using PUT statement? 

[Or]

@Michael_Tarallo  Follow-up question regarding this. The help site says, regarding LOAD in chart level scripting:

"The LOAD statement loads fields from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent SELECT statement or by generating data automatically. It is also possible to load data from analytic connections." (Source: https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartLevelScripting/modi... / Load section, emphasis mine)

I was unable to get this to work with files, previously loaded tables, or subsequent selects - is this actually possible, and if so, could you share example syntax? I was only able to get this to work with Autogenerate() and all of the samples only use Autogenerate.

Thanks!

[Kdober]

Hi @Michael_Tarallo 
Seems like a great feature. Will this be available on Client managed (on premise) version as well?

Thanks!

[Or]

@Kdober This feature is available in the August 2022 release of the on-premise version (it is, in fact, pretty much the only new feature in that version)

[Michael_Tarallo]

Hi Guys - I have informed a few people to take a look at these questions. If you do not hear anything, please contact me. 

[marcogiannuzzi_1990]

Hi everyone,

yesterday I thought an useful use case (my linkedin post in the following link), creating a total bar in a chart usig the qliksense chart level script.
Furthermore, let's assume we have the following dataset:

SalesData:
Customer,Sales
Customer1,10
Customer2,15

And we create a bar chart, with Customer as Dimension and NoOfSales as Measure (note NoOfSales is the label for the sum(Sales) measure); in this case, we'll have two bar, each one corresponding to a distinct Customer.
Imagine that we have to develop a third bar (a total sales bar) , using the chart following chart level script:

Let P = HCNoRows(); //this variable will equal to 2, the current number of bars

let measureTot=0;//i create a variable, by default equal to 0

//for each bar in the chart, I will sum the measureTot variable with its NoOfSales measure (e.g. 0+10+15)

For J = 1 to P 

let measureTot=measureTot+HCValue(NoOfSales ,J);

Next;

//after the loop my measureTot variable will be equal to 25, but the bar is still not in the chart. I have to add a new, virtual, bar... adding a dimension label 'Total' and as measure measureTot 

add load

'Total' as #hc1.dimension.1,

$(measureTot) as #hc1.measure.1

autogenerate 1;

recap

 
Bests!
Marco Giannuzzi

[Gerhard]

Thanks for this, I had to modify it slightly as mine did not work using the label names. I borrowed some code from @marcogiannuzzi_1990 which is in this post so thanks for that.

In summary I used the #hci1.dimension and hci1.measure rather than label names.

//Gets the total no of rows in the current Chart or Table
Let P = HCNoRows();
//Declaring Quarterly Variable
Let vQ1Total = 0;
Let vQ2Total = 0;
Let vQ3Total = 0;
Let vQ4Total = 0;

For J = 1 to P
//getting the value of the Dimension for a row
Let vDim = HCValue(#hc1.dimension.1, J);
//Control Statement, to check and accumulate the numbers to the right variables
if Match(vDim,'1','2','3') then
Let vQ1Total = vQ1Total + HCValue(Sales,J);
elseif Match(vDim,'4','5','6') then
Let vQ2Total = vQ2Total + HCValue(Sales,J);
elseif Match(vDim,'7','8','9') then
Let vQ3Total = vQ3Total + HCValue(Sales,J);
elseif Match(vDim,'10','11','12') then
Let vQ4Total = vQ4Total + HCValue(Sales,J);
end if
Next
//If $(vShowHideTotal) = 1 then
//Using the combination of Prefix and Regular statements to add the additional rows to the Hypercube
Add Load 'Q1' as #hc1.dimension.1, $(vQ1Total) as #hc1.measure.1 Autogenerate 1;
Add Load 'Q2' as #hc1.dimension.1, $(vQ2Total) as #hc1.measure.1 Autogenerate 1;
Add Load 'Q3' as #hc1.dimension.1, $(vQ3Total) as #hc1.measure.1 Autogenerate 1;
Add Load 'Q4' as #hc1.dimension.1, $(vQ4Total) as #hc1.measure.1 Autogenerate 1;

//endif

[Or]

Adding another question - has anyone been able to use Put on dimensions? I was not able to get this to work using either Put DimName(1) = 1 or Put #hc1.dimension.1(1) = 1. Using Put on measures works fine, though.

[barnabyd]

Thanks @Michael_Tarallo  and Venkat,

I look forward to seeing all the use cases that this feature can help with.

However, I'm curious about performance. I have always tried to move as much of the calculations as I can out of the charts and back into the load script. The reason is that when the data set become very large, say over 100,000 rows, then performance really suffers if you have too many calculations at the point when the user  makes a selection. I have a client with over a million rows of data and they had some complex set analysis (several hundred lines of code) for each measure. The app was taking several minutes to respond to user interactions. By moving calculation back into the load script, I was able to reduce the response time down to about 10 seconds, which I still think is too long.

Do you think that this is going to be an issue with Chart Level Scripting? How do we get the right balance between load script and chart script?

Thanks, Barnaby.

[tan123qlik]

@Michael_Tarallo any news on the question from Or? Regarding loading data from existing (resident) tables.

I have tried but get "Unexpected dynamic script error".

Add Load
    Dim1 as year
Resident Tmp
;