2015

So you're interested in building custom charts in Qlik Sense mashups but don't know where to begin? Let's walk through building one.

 

The basic flow of building a custom chart goes like this -

1. Get a hypercube

2. Format data as needed

3. Draw or update chart

 

STEP 1 - GET A HYPERCUBE

We will use the Qlik Dev Hub to make creating our hypercube easier (information on the dev hub here). Open the dev hub and create a new mashup using whichever template you'd like (I chose the grid template). Now, choose an app from the dropdown near the top left of the edit window.

2015-11-25 12_17_31-Mashup editor _ Developer Hub - DevHub.ProductQlikSense.png

 

Now, let's create the hypercube. Click the 'Add hypercube' button in the top right of the editor window, and a dialog will popup. Choose the dimension(s) and measure(s) you will need to create your chart. Set the rows to the number of rows of data you would like to include in your chart, and enter a name for a callback function. Then click 'Add'.

2015-11-25 12_26_08-Mashup editor _ Developer Hub - DevHub.ProductQlikSense.png

 

Now a method to create a hypercube, and the callback function will have been added to your .js file. I chose to add a hypercube using the 'Month' field as a dimension and the 'Margin Amount' measure as my measure, using the Consumer_Sales app. If you want to follow along with me exactly, and recreate the "Margin Amount Over Time" chart from the Consumer_Sales app, you will have to manually edit the hypercube that has been generated a tiny bit. By default, a hypercube sorts the data by the load order of the first column, usually your first dimension. In order for months to be in the correct order in the data I will get back from my hypercube, I can sort the "Month" dimension numerically. The picture below shows the code I added to my hypercube, and you can check out all the properties that can be set on a hypercube here.

2015-11-25 12_41_05-Mashup editor _ Developer Hub - DevHub.ProductQlikSense.png

 

 

STEP 2 - FORMAT DATA

The callback function you specified while creating the hypercube will be called when the data for the hypercube is returned from the engine, and every time the data in the hypercube changes. I like to log the reply as a first step, just to make sure my data is coming through correctly, and check out what it looks like.

2015-11-25 12_48_02-Mashup editor _ Developer Hub - DevHub.ProductQlikSense.png

In the console, you can then check out the reply you get. Now, you'll want to figure out how you can format the data in the reply to use in creating your chart. Obviously, this is dependent on how you'll be creating your chart. For this example, I've chosen to use c3.js, but there's a good number of interesting and useful javascript charting libraries, any of which you can make use of.

 

An example of a c3.js line chart can be found here. Now that I know what my data basically needs to look like, I can format it. Below is a screenshot of the code I used to format the data.

2015-11-25 12_59_59-Mashup editor _ Developer Hub - DevHub.ProductQlikSense.png

STEP 3 - DRAW OR UPDATE THE CHART

Now that I have created a hypercube, and formatted the data, I need to actually draw the chart, and handle any time the data is updated. How you accomplish this can vary wildly. For instance, you could create a chart each time the data is loaded or changed. This would look something like creating the chart and appending it to a dom element, and every time data is updated, clear the dom element and recreate the chart and append it to the dom element again. I usually prefer to only create the chart once, and then just update the chart, if possible. With c3.js, this is very easy as it has a built in method for simply updating data on an existing chart. That means the only thing I really have to worry about is to check and see if the chart exists yet, and if it does not then create the chart, and if it does then just update the data. That looks like this -

2015-11-25 13_16_37-Mashup editor _ Developer Hub - DevHub.ProductQlikSense.png

 

SUMMARY

We created a hypercube, and then in the callback function for the hypercube, which gets called every time the data in the hypercube loads or changes, we formatted the data into a usable format for our chart, and then either drew the chart or updated the chart based on whether it already existed. Read through again, check out the full project, attached, and also live at http://webapps.qlik.com/c3-chart-example/c3-charts.html and let me know if you have any questions.

For any BI tool to work it needs to understand who is using the tool. If we know who is using the tool (authentication) we also need to understand things about them such as where they work, e-mail address etc. If we know who the user is and what describes him/here we can do things like giving them access to the right information and tailor their experience. We therefore have methods in both QlikView and Qlik Sense to access information describing the users in user directories. Even though we have this capability in both QlikView and Qlik Sense, their approach to doing it is very different.

 

The most common user directory used by our customers is LDAP. LDAP is a distributed database that is suitable for maintaining user information.  It exists in both open solutions developed by the open source community (such as OpenLDAP) and in closed solutions (such as Microsoft Active Directory).

 

So what are the differences between QlikView and Qlik Sense?

 

QlikView is based on the concept with a directory service connector that fetch user information as it is needed. It doesn't replicate any data stored in the LDAP database, but instead asks a question every time it needs access to user information including groups.

 

Qlik Sense uses a sync approach, which means that it replicates the user information including groups from LDAP into a database and then keeps this in sync. This can be done in different ways (sync all users, sync with a filter, sync only users that access Qlik Sense) depending on your needs and size of directory.

 

There are two major usages of the user information, each with different needs: for management and for access control/authorization.

 

For management it is good if the information is current but not absolutely critical, so here the Qlik Sense scheduler is used to update the data.

 

For security however, it is critical that information is current. Therefore individual user records are updated as the user connects to the Qlik Sense environment.

 

So, are there any similarities in how you configure Qlik Sense and QlikView?

Yes

 

In the picture below I describe the relations between QlikView LDAP configuration and Qlik Sense LDAP Configuration.

QlikView to Qlik Sense Mapping.png

 

I hope that you found these tips on how we access user information helpful. If you have questions on this blog post or have ideas of what you want to read about in the future, please don’t hesitate to add comments to post.

Do you even need to delete or keep some characters in a string field?  The PurgeChar and KeepChar functions allow you to purge and keep characters that are in a string.  The PurgeChar function takes two parameters.  The first is the string and the second is the character(s) that are to be removed from the string.  The KeepChar function also takes two parameters but in this case the second parameter is the character(s) that are to be kept in the string.  Let’s take a look at some examples.

 

Sometimes you may have a dataset that has garbage in it like in the FirstName field below.

FirstName.png

In this case there are characters after each name that I do not need.  In order to remove these characters from the field, I can use the PurgeChar function in my script (see below) to remove all the unwanted characters from the FirstName field.

Purge script.png

Once I run the script the names look like this:

FirstName Clean.png

The KeepChar function works similar except in this function you indicate what characters you would like to keep.  This may be helpful when you have field that includes a mix of numbers and letters but you only want to keep the numbers or the letters.  In this example, I have a ProductCode field that has codes that are made up of numbers and letters but I only want the numeric data.

ProductCode.png

In my script, I can use the KeepPurge function and use the second parameter to list all the numbers since those are the characters I want to keep in the string.

Keep script.png

The end result looks like the image below.  The product codes are now all numeric and the letters have been removed.

ProductCode Clean.png

There are a host of string functions that can be used to clean up or modify a string but when there are specific characters that you need to remove or keep, PurgeChar and KeepChar can be helpful and easy to add to your script or chart expression.  These functions work well when you need to remove or keep ALL references to a character in a string.

 

Thanks,

Jennell

Henric Cronström

Accumulations

Posted by Henric Cronström Nov 10, 2015

When building analytical applications you very often encounter cases where you want to accumulate numbers inside a chart. It could be that you want to calculate a Year-to-date number, or a rolling 6 month average, or a moving annual total.


When creating an accumulation, there are two fundamental challenges. First, there is the question on how to treat dimensional values with no data. The tables below illustrate the problem:

 

Missing month.png

 

In this example, there is no data for the month of March. The default behaviour is that this row then is omitted, see left table. This leads to an incorrect accumulation – the rolling 3 total for April includes January, which it shouldn’t. The table to the right however treats missing months correctly.

 

Secondly, there is the question on how to treat the first dimension when there are several dimensions. The tables below illustrate the problem:

 

First dimension.png

 

In the left table, you want the accumulation to re-start for each new product. You do not want the accumulation to continue from the previous product. Note that for ‘2014 Jan’ the accumulation is reset.

 

But in the right table, it is the other way around: A moving annual total should span over two calendar years, so you want the accumulation to continue over into a new value of the first dimension.

 

In QlikView and Qlik Sense there are several ways to create accumulations, each with its own pros and cons.

 

1. Using the control in QlikView charts

In a QlikView chart, you can easily create an accumulation just by selecting the right chart setting:

 

Chart UI accumulation.png

 

This method will however fail both the above challenges. Also, an accumulation can only be made over the first dimension, and the accumulation will always be reset for a new value in the second dimension.

 

Further, you cannot use it to calculate moving averages. It only works for calculations of sums.

 

2. Using the Above() function

If you use the Above() function in a chart table like

 

     RangeSum(Above(total Sum(Amount) , 0, 12))

 

or in an Aggr() table like

 

     Only(Aggr(RangeSum(Above(total Sum({Amount),0,12)), YearMonth))

 

you will get a 12-month moving total. It will pass the “multiple dimensions” challenge: By using – or not using – the total specifier in the Above() function you can decide which behaviour you want.

 

But it will fail the “no data” challenge. There is a however a way to make an accumulation in an Aggr() table work also for this problem: Trick the QIX engine to show all rows by adding a zero term for all dimensional values:

 

     Only(Aggr(RangeSum(Above(total Sum(Amount) + Sum({1} 0),0,12)), YearMonth))

 

and enable the display of zero values. Then you will get the right numbers also when some dimensional values are excluded.

 

3. Using an As-Of table in the data model

The As-Of table, is an excellent solution that will pass both challenges. Here you must set the condition using Set Analysis:

 

     Sum({$<MonthDiff={"<12"}>} Amount)

 

However, it has one drawback: When a user clicks in the chart, a selection will be made in the AsOfMonth. But you don’t want the user to make selections here: You want month selections to be made in the real month field. So I usually make my charts read-only if they use the AsOfMonth as dimension.

 

With this, I hope that you understand accumulations better.

 

HIC

 

Further reading related to this topic:

The Above Function

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

In many of my webpages that I've created using the Capabilities API, I had to create a custom menu and I always used Bootstrap's Dropdown component. In this tutorial I will show how to do the same.

 

From the Mashup Editor, create a new project and give it a name, using "Grid Mashup Template". this will include bootstrap for you.

.1.png

2.png

 

Select the app "Helpdesk Management.qvf".

 

From the right hand side, create a list and select "Department" from the Dimensions. Make sure you add a callback function, since that is where we will put our code. Here, I named it showData.

3.png

 

Now, lets go to the html template and add the dropdown code.

In the div that has the id="QV01", add the following:

 

  <div class="col-sm-4 qvplaceholder" id="QV01">
   <div class="dropdown">
  <button class="btn btn-default dropdown-toggle" type="button" id="dropdownMenu1" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true">
   Select Department
   <span class="caret"></span>
  </button>
  <ul class="dropdown-menu" aria-labelledby="dropdownMenu1">
  </ul>
   </div>
  </div>






In your javascript code, you will see a callback function created.


//callbacks -- inserted here --
function showData(reply, app){}





 

The reply has all of the data from the Dimension so we can create the list elements in the drop down. So, let's populate the drop down list elements.

 

function showData(reply, app){
     $('#QV01 .dropdown ul').empty()
     $.each(reply.qListObject.qDataPages[0].qMatrix, function(key, value) {
          if (typeof value[0].qText !== 'undefined') {
               $('#QV01 .dropdown ul').append('<li><a data-select="'+ value[0].qText+'" href="#">'+ value[0].qText+'</a></li>');
          }
     });
}




 

Notice that we empty the list first, otherwise every time we make a selection, the same elements will be added in the drop down.

 

Now, we have to add a jQuery on click event that will trigger the selection and change the button value.

$('body').on( "click", "[data-select]", function() {
     var value = $(this).data('select');
     app.field('Case Owner Group').selectValues([value], false, false);
     $('#QV01 .dropdown button').html(value + ' <span class="caret"></span>');
  });




 

The selection drop down is ready. now lets add some more graphs to make it more interesting when we select a department. I added 'Open & resolved cases over time', the KPI 'High priority Cases' and 'Case Details'. So the final page should look like this:

4.png

 

Last, we must set the button to the original state after the user clicks on the "Clear All". We just add the following line in the code.

 

case 'clearAll':
     app.clearAll();
     $('#QV01 .dropdown button').html('Select Department <span class="caret"></span>');
     break;




Henric Cronström

The As-Of Table

Posted by Henric Cronström Nov 3, 2015

Last week I wrote about how the Above() function can be used for calculating rolling averages and other accumulations. There is however also an alternative method for doing the same thing:


The As-Of table.

 

When you use the Above() function, you fetch a number from other rows in a chart or Aggr() table. The As-Of table is slightly different in this respect: It is not a transient table created by an object or an expression – instead it is a real table in the data model.

 

The idea is to create a secondary month field – the AsOfMonth - that links to multiple real months.

 

Fields.png

 

In the example above, you can see that ‘2015 Oct’ links to several preceding months, and each Month in turn links to several rows in a fact table. This means that a specific transaction will be linked to several AsOfMonths.

 

In the data model, the As-Of table should appear as a separate calendar table that links to the existing primary calendar table:

 

DataModel.png

 

One way to create this table is the following:

 

First, make sure that you in your master calendar have a field “Month” that is defined as the first date of the month, e.g.

 

Date(MonthStart(Date),'YYYY MMM') as Month,

 

Then add the following lines at the end of the script:

 

// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct Month
  Resident [Master Calendar] ;

 

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
  Resident tmpAsOfCalendar ;

 

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
  AsOfMonth,
  Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
  Year(AsOfMonth)-Year(Month) as YearDiff
  Resident tmpAsOfCalendar
      Where AsOfMonth >= Month;

Drop Table tmpAsOfCalendar;

 

Once this table has been created, you can use the AsOfMonth as dimension in charts where you want rolling averages and accumulations.

 

If you as measure use

 

Sum({$<YearDiff={0}>} Sales)

 

you will get a yearly accumulation – year-to-date up until the day of the script run.

 

Yearly accumulation.png

 

If you instead use

 

Sum({$<MonthDiff={"<6"}>} Sales) / Count(distinct {$<MonthDiff={"<6"}>} Month)

 

you will get a 6-month rolling average:

 

Rolling average.png

 

And finally, if you use

 

Sum({$<MonthDiff={0}>} Sales)

 

You will get the real, non-accumulated numbers.

 

I have made the Set Analysis expressions based on two fields: YearDiff and MonthDiff. However, for clarity it could be a good idea to add flags in the As-Of table, so that the Set Analysis expressions become even simpler, e.g.

 

If(MonthDiff=0,1,0) as IsSameMonth,
If(YearDiff=0,1,0) as IsSameYear,
If(MonthDiff<6,1,0) as IsRolling6,

 

Summary: The As-Of table is a good way to calculate rolling averages and accumulations.

 

HIC

 

Further reading related to this topic:

The Above Function

Accumulations

Calculating rolling n-period totals, averages or other aggregations

Filter Blog

By date:
By tag: