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

[ms_12]

@Michael_Tarallo  Can we solve canonical date problem with chart level scripting?

[WangKun]

I am wondering whether store function is supported in chart level scripting.

[stevejoyce]

Any new chatter on this topic?  

I wasn't able to use statements like: replace or load resident table (certainly not load from external file).

Only able to do the add load with autogenerate.

I was unable to sort the object with new field after creating rows.  Specifically i was trying to add subtotal rows with a value in a sort field, but object always sorts these added rows at the bottom.

Only data could be referenced that were part of the object itself.  I expected this would be the case, but was hoping i could do something like...

let vAccount =HCValue(#hc1.dimension.1,J)

let vAccountCat = only({<account{$(vAccount)} >} account_category)

so that i can reference other related data based on J's value, but in this case i still needed account_category to be a dimension in the table.  Still no "hide" column in native qsense tables 😞

Mostly left with being able to do cumulative or adding total rows to end of hypercube that didn't give too much added benefit.  Seems potentially powerful but not enough community info to give it traction for me.

[Hein_Schultz]

@tan123qlik It seems you don't reference a data table in Resident, but rather the Hypercube created by the chart, so your resident must be HC1. This is the same for the Fields - So if you are using field MyDimension as ShowDimension and a SUM of MyQuantity as ShowQty, then you should reference ShowDimension and ShowQty for the script.

In my test, I am also getting missing from hypercube error when attempting to add new data from another table onto the displayed columns (let's say some type of target for the bars to compare against). I added a simple script as

Add Load
'JustTesting' as ShowDimension ,
sum(TestValue) as ShowQty
Resident OtherTable;

If I understand correctly, then this functionality should not be mistaken for the backend script itself, but rather the scripting language for use in the cube only.

[kkrehbie]

I'd also add I'm a bit confused on what is allowed when using a LOAD statement.

As of 3/17/2023 the official documentation shows:

In a chart modifying context, the LOAD statement loads additional data to the hypercube from data defined in the script, or from a previously loaded table. It is also possible to load data from analytic connections.

But I get errors when trying to load data from outside the hypercube, either from a table in my data model or from an external file.

Just trying to make sure the documentation is updated to match the functionality, or maybe some example can be provided so that I can double check my syntax 😉

[JHuis]

@kkrehbie ,

did you get this to work?

I am also trying to add data trough chart level scripting. 

[robert99]

Thanks for the example. Its hard to find much on this feature. I know the first 2 are only examples but I wouldn't use for tables (except maybe for my own use if nothing else worked) because of the following issues

You can't change the column order as the script is linked to a column (measure number 1,2 etc)

For accumulated date periods it's not possible to filter by months ie. if filter by say Oct Nov Dec it will only accumulate from Oct when the year might start in Jan. (likewise 'range sum - above' but not as-of period)

It takes time to set up. And learn. When other options are more robust 

And other ways like As-of period are much easier to set up. And aren't impacted by filtering issues. Moving column orders. Or changing the row order.

I think rangesum-above is much easier. It has the same issues but column orders can be changed 

Will think about @marcogiannuzzi_1990  example above

[robert99]

I noticed the measure for Actual = Sum(Amount) worked as well as Sum(0)

//Secondary loop to generate the Running total and the percentage
For i = 1 to r
//Picks the value of the Measure Actuals and adds it back to the vTotal variable
Let vTotal = vTotal + HCValue(Actuals,i);

//Pushing the computed value to the Running Total Measure
Put #hc1.measure.2(i) = $(vTotal); //measure 2

//Pushing the computed value to the Running Total Measure
Put #hc1.measure.3(i) = $(vTotal); //measure 3
//Calculating and Pushing the computed value to the % Measure
Put #hc1.measure.4(i) = $(vTotal)/$(vGrandTotal); //measure 4
Next

[Or]

@robert99 You can use the measure name explicitly rather than the measure number, e.g.

Let vRunningTotal = HCValue(Sales,J);

or

Put RunningTotal(J) = vRunningTotal

I've yet to find any use cases for script-level charting other than running totals and row numbers that don't break when you hide zero/null (hiding zero/null often breaks the "standard" running totals we'd use in Qlik). I suppose it might be used to create a more efficient dynamic Opening Balance in a case where the user wants to select a period / set of periods and everything that happened before that period is used to create the opening balance - but I haven't tried it and I'm not sure if it'd actually be more efficient.

[george_kos]

@Michael_Tarallo it seems there is an issue with sorting when using chart level scripting. The values on dimension level will always go at the end of the chart. For example, if my dimension has Jan, Jun, Sep only and I add values for Feb and Mar, these values will always show at the end after Sep, no matter what.

Is there a way to make this work properly?