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

[KeaneGleeson]

Has anyone encountered an issue where your dimension swaps out your selections for other possible values in the dimension field? I am finding that when I apply selections on the field I am using for my bar chart it seems like it is swapping my selections for maybe the first loaded values in the table.

E.g. I select sites 4,5,6 but it shows the labels for sites 1,2,3 on the chart.

I am trying to add a simple variable value into a separate bar with this:

add load
'Benchmark' as #hc1.dimension.1,
$(v_benchmark_volume) as #hc1.measure.1
autogenerate 1;

[diagonjope]

Hi @Michael_Tarallo ,

Given that Qlik Sense Enterprise on Windows has a limited number of options for calculating Totals in straight tables, and there is no option to use an expression, I have been trying to use Chart Level  Scripting to modify the values of the Totals row in a very simple test table, like the one below:

I can change the values of the measures in rows 1 & 2, but I have not been able to determine the row position for the corresponding Total in any of the measures.  Is there a row <value> for the Totals row, so that one can change it using a "Put #hc1.measure.2(<value>) = <expression>;" statement?  Do you know if there is any other way to accomplish this?  

Please advise.

Cheers,

++José

[robert99]

@diagonjope 

"Given that Qlik Sense Enterprise on Windows  has a limited number of options for calculating Totals in straight tables,"

Solved: QlikSense Profit and Loss Statement without an ext... - Qlik Community - 1512744

or maybe use ValueList()

[diagonjope]

Hi @robert99 !

Thanks for your suggestions.

The visualization example that I provided had the intention of illustrating the issue / limitation in the simplest case possible. 

We can not use ValueList() because we don't know the values beforehand.  While aggregating calculations at the data load stage does not work either, because the cell values will change depending on the field values selected. 

Since we can not use an expression to calculate the "real" Totals, we have to do the calculation after the Qlik engine (QIX, or whatever is called today) calculates the hypercube - as if we were working with a spreadsheet.  Hence the interest in Chart Level Scripting. 

The challenge is that we don't know where the Totals are stored in the hypercube.

Cheers,

++José

[robert99]

@diagonjope 

"While aggregating calculations at the data load stage does not work either, because the cell values will change depending on the field values selected"

The first option above doesn't do this. It calculates a total which will change depending on the filters made.

But chart level scripting is a disappointment. I was hoping it would allows an equivalent to a PBI DAX feature. Where a temp table can be set up with a set of filters and then the resulting calculated measures can be input into another measure. With different filters.

Its one of a number of releases that need a rethink.  

[diagonjope]

Greetings @Michael_Tarallo !  Can you please route this question to the team in charge of Chart Level Scripting?  I really need to solve this for a customer, and it seems that there is no one in the Qlik Community who can shed some light on how to change the values in the Totals row of the chart using CLS.

Cheers,

++José