In this article we will learn how to give more freedom to business users, by allowing them to create their own data tables from predefined list of expressions and dimensions. Let’s start!
Our task in this example is to provide dashboard to HR where both dimensions and expressions can be defined by the user. We will do this by providing list of fields, which can be used as dimensions and list of formulas which will be used as expressions. In addition – we will then allow them to change data representation and add a way to save their creations for future usage. We will also add some predefined templates for most used scenarios.
First we will need some data. I’ve generated mine on http://www.generatedata.com/ and saved it into Excel:
Let’s load this data now and add one calculated dimension [Hire Year]:
Employees: LOAD *, Year([Hire Date]) as [Hire Year] ; LOAD ID, [First name], [Last Name], Phone, Email, Country, City, Department, Date#([Hire Date],'DD/MM/YYYY') as [Hire Date], [Personal Number], Gender FROM [Employees.xlsx] (ooxml, embedded labels, table is Worksheet);
Next we will need the list of Expressions and Dimensions, for HR to choose from:
Dimensions: LOAD * INLINE [ Dimensions ID First name Last Name Phone Email Country City Department Hire Date Personal Number Gender ]; Expressions: LOAD * INLINE [ Expressions Headcount Joiners YTD ];
So we’ve got some employee data, list of expressions and dimensions. We can now make use of those and add them to report:
Let’s add “Straight Table” chart with all employee dimensions and two expressions:
1. #hc– to calculate headcount:
2. #jty – for joiners in current year
Final object will look like below:
Fine, but we wanted our users to create their own chart, only based on our dataset. Do not worry – we are getting there!
We will make use of “Dimensions” and “Expressions” List Boxes, combined with “Conditional” tick-box in Straight Table chart.
Idea here is to only show columns, which are matching selections in “Dimensions” List Box and use expressions chosen by user from “Expressions” List Box.
Let’s start with making #hc expression to activate on demand. Go to Chart Properties -> Expressions -> Select #hc expression -> Tick Conditional box -> Paste below code into field under the box:
GetSelectedCount(Expressions)>=1 AND concat(Expressions,',') like '*Headcount*'
You should have something like this:
What does this do? Simply makes sure that expression is selected in “Expressions” List Box and that “Headcount” is one of those expressions.
We must follow same road for all dimensions as well. For example, ID will use below Statement for Enable Conditional:
GetSelectedCount(Dimensions)>=1 AND concat(Dimensions,',') like '*ID*'
We can now create a Chart as we please, and play around with data aggregation. Example for Headcount by Gender:
You probably spot the small “bar chart icon” in the top right corner of the screenshot. It is there as I am allowing for “Fast Type Change”. It can be set in Chart Properties -> General -> Fast Type Change:
When user right clicks the icon, small pop up menu shows up, allowing to change chart’s type:
Clicking pie chart icon, changes straight table to pie chart, etc.:
It is always a good idea to give users some predefined templates, which we know are commonly used, and bookmarks are great to provide that functionality.
Simply do selections and add a server bookmark, for everyone to benefit from:
Users can do same thing from QlikView Access Point and save their work for future reference.
That’s it! If you have any questions, please comment below!
You can download sample qvw below: