Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
- Customization
- Flexibility
- Maintainability
I have found it especially useful where working with financial reports when performing the following:
- Running totals (Sounds so simple)
- I have more complex % to be calculated based on business rules
- 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:
- Assumption : Enabled Chart Level Scripting
- I have created a straight table with
- Dimensions: Division and Financial Month
- Measure: Actuals (Sum(Actuals))
- Create 2 additional dummy measures
- Additional Measure 1: Give Sum(0) as the Expression and Running Total as the label
- Additional Measure 2: Give Sum(0) as the Expression and % as the label
- 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:
- Assumption : Enabled Chart Level Scripting
- I have created a straight table with
- Dimensions: Division and Financial Month
- Measure: Actuals
- Create 2 additional dummy measures
- Additional Measure 1: Give Sum(0) as the Expression and Running Total as the label
- Additional Measure 2: Give Sum(0) as the Expression and % as the label
- Additional Measure 3: Give Sum(0) as the Expression and Divisional Total as the label
- Additional Measure 4: Give Sum(0) as the Expression and Divisional % as the label
- 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
- Control statements
- Regular statements
- Prefixes (We will look at this in example 3)
Example 3: Aggregated measures and dimensions in a bar graph
Steps:
- Assumption : Enabled Chart Level Scripting
- Drag and drop a bar graph on a sheet and
- Dimensions: Financial Month
- Measure: Actuals
- 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
- Turning on chart level scripting ‒ Qlik Cloud
- Using chart level scripting and Limitations of Chart Level Scripting ‒ Qlik Cloud
- Chart level scripting ‒ 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