Point In Time Reporting - Qlik View

One of the most common use cases of Set Analysis is Point In Time Reporting. Having the ability to perform period-over-period analysis is a basic requirement in any BI tool and is easily performed in QlikView with the aid of set expressions. However, needless to say, Set Analysis is also amazingly useful for the fulfillment of other special requirements.

Let's combine the acquired knowledge and apply it to add Point In Time Reporting to our Airline Operations document.

The challenge

HighCloud Airlines' executives require a dashboard to easily compare different performance indicators in a period-over-period basis. The different period comparisons they need are:

  • Current Year-To-Date indicators versus the same period last year
  • Current month versus same month last year
  • Current month versus previous month

The comparisons should be dynamic and based on the user's selections. So, if the user selects October 2010, the corresponding comparisons should be:

  • From January through October 2010 versus January through October 2009
  • October 2010 versus October 2009
  • October 2010 versus September 2010

At the same time, all user selections across the different Airline, Aircraft, or Airport attributes must be taken into consideration in all point-in-time analyses.

Let's work some Set Analysis magic.

Defining the set modifiers

We'll start tackling each requirement by first defining the set modifiers each period comparison would need. For that, we must remember that the current time-related fields in which the user is able to make a selection are Year, Quarter, Month, and Period.

However, not always will the user have explicitly selected values in all fields. There can be selections in Year only, for instance, or Year and Month, or only Month, or even no selection whatsoever.

The set modifiers we define when building our expressions need to account for all of the possible scenarios and always show a clear result. This will ensure the user is not confused as to what filters are being applied when navigating the document.

Even if the user has not made specific selections on all time-based fields, we can easily infer a period in which to base our comparisons by taking the most-recent period from the list of associated values. Let's suppose the user has the following selection state:

  • Year = 2010
  • Quarter = Q2

From the previous example, we can infer that the "current month" (our base period) is June 2010, as it is the most recent period in the list of possible values.

If, on the other hand, the user has nothing selected at all, we will take December 2011 as our base period, as it is the latest month available in the dataset.

Obtaining the base period record set

A simple Max(Period) expression will help us get the base period in all scenarios, as the Period field contains both the Month and Year components. Therefore, the set modifiers that we would use to get the record set corresponding to the base period in each of the required comparisons are:

  • Current Year-To-Date indicators:
    <Period = {"<=$(=Max(Period))"}, Year = {"$(=Max(Year))"}, Quarter = , Month = > This set modifier will result in a record set containing all of the periods that are less than or equal to the current period, and belong to the current year, which would be all Year-To-Date records.
  • Current month:
    <Period = {$(=Max(Period))}, Year = , Quarter = , Month = > This set modifier will result in a record set corresponding to only the current period.

We have defined some "disregard" fields in our set modifiers because, depending on user's selections, the base period we are trying to retrieve might already be excluded from the active record set. Therefore, we need to override the restricting selections to be able to access the periods we need.

Now that we have the base period, we are halfway through. We just need to construct the set modifier for the compare-to period, which is a little trickier to obtain and will naturally be different for each different period comparison.

Obtaining the compare-to period record set

The easiest of the compare-to periods to obtain is the "previous month." We would use something such as Max(Period) - 1 to obtain its value. However, this expression doesn't always work for our purposes. As pointed out previously, when our base period is January, the previous month obtained using the above expression would be an nonexistent one. For example, if the current period value is 201101 (remember the field is in YYYYMM format), the expression would return 201100.

One way we could solve it is adding an If function to the expression to account for those particular scenarios:

However, this is very impractical, so here is another approach: instead of using the actual period numeric representation in the YYYYMM format, we can assign each of the period a new numeric ID using the Autonumber() function in the script. That way, all periods in the calendar table will have a consecutive number assigned with which we can easily use in our frontend calculations. Follow these steps:

  1. Open the Airline Operations.qvw document and launch the Edit Script window.
  2. Activate the Calendar tab and modify the Master Calendar Load script by adding the following code between the table name and the first load distinct statement:
  3. Save and reload the script to apply the changes.
    We simply added a new preceding Load statement to create a new PeriodID field by taking the result in the Period field, which is being created in another preceding Load, and applying the Autonumber() function to it using a PeriodID counter. As the calendar is being populated in ascending order, each new period will have a PeriodID value that is the consecutive to its previous period.
  4. Create a temporary table box with all of the calendar-related fields to better visualize the contents of the Master Calendar table. It should look as follows:

    Point-In-Time-Reporting-Figure


    The Autonumber() function will only create the correct IDs in chronological order when the calendar table is being loaded or populated in ascending order. If that's not the case, an alternative formula to generate a consecutive PeriodIDfield in the script would be:
    This formula will, for example, assign the value 24120 to December 2009, and 24121 to January 2010, and so on.

Now that we have the corresponding PeriodID field, we can use a simple expression to retrieve the value for the previous periods. This will account for every scenario:
Max(PeriodID) – 1

The corresponding set modifiers to obtain the compare-to periods are:

  • Previous Year Year-To-Date
    <PeriodID = {"<=$(=Max(PeriodID)-12)"}, Year = {"$(=Max(Year) -"}, Quarter = , Month = , Period = > This set modifier subtracts 12 from the current period's ID field to obtain the corresponding period from last year. It also subtracts 1 from the Year value to obtain the previous year. As we are now using the new PeriodID field, the old Period field is specified as an ignored-selections field.
  • Same month last year
    By subtracting 12 from the current period's ID, we obtain the corresponding month from last year.
  • Previous month
    By subtracting 1 from the current period's ID, we obtain the previous month's record set.

Now that we have defined our set modifiers, it's time to construct the expressions.

Constructing the expressions

Using the base and compare-to set modifiers that we just defined, our final expressions will be as described here.

The following expressions are constructed in the following form to obtain a variance percentage:

(BasePeriod / CompareToPeriod) - 1
  • Current Year-To-Date versus the same period last year
  • Current month versus same month last year
  • Current month versus previous month

However, the expressions alone are nothing if we don't create some charts to make use of them. Take a moment to create some visualization objects, such as gauge charts, straight tables, and so on, that allow HighCloud Airlines' executives to get the performance overview they need in terms of period-over-period relative growth.

We have included some chart examples in the solution I file corresponding to this chapter.

Enabling additional period comparisons

The same concept used to build the PeriodID field can be used for the QuarteriD field. We can easily and seamlessly create a lot of expressions for Point In Time Reporting after we've created the Calendar table with at least the following fields:

Year, Month, Quarter, PeriodID, and QuarteriD.

Take a moment to add the QuarterID field to the master calendar table by using the following script expression:

AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID]

Therefore, the final Master Calendar Load script will be:

Save and reload the script to apply the changes.

More Point In Time Reporting examples

Let's quickly review some common Set Analysis expressions that we can use when required. The following expressions calculate the total number of flights for different specific periods of time:

  • YTD (Year-To-Date) flights:
  • QTD (Quarter-To-Date) flights:
  • MTD (Month-To-Date) flights:
    The preceding MTD expression is actually a current-month expression. To construct a MTD expression, we should also include a Date field, which we don't have in our example document.
  • Previous Month flights:
    When the QlikView document contains data at a daily level, the previous month calculation can be defined to only account for the same number of days as the days so far in the current month. This would be done adding a new field value definition, based on a Date or Day field, to our set modifier.
  • YTD flights for the previous Year
  • Flights for rolling-12 months:
    There you have it, a complete set of formulas to help you create Point In Time Analysis in your QlikView documents in a very simple fashion. We invite you to try them out with the Airline Operations document and build some context around the already created dashboard.

Storing set expressions into variables

Period-over-period comparisons are widely used in QlikView documents and, as you've seen, they can get somewhat messy. Instead of writing the set expression each time for every expression in which it is used, it's a good practice to store its definition in a variable, which can then be called from anywhere in the QlikView document where it's required.

Take, for example, the following expression. It calculates the number of flights Year- to-Date, based on user's selections:

From the preceding calculation, we can take the set modifier part (the part which is enclosed in angle brackets) and define it as a new variable, called vSetYTD. Then, we would use this new variable into a modified version of the expression presented above as follows:

Sum({$<$(vSetYTD)>} [# Departures Performed])

Look closely. You'll see we are inserting the set modifier into our formula with the use of the Dollar Sign Expansion syntax, which results in the exact same expression as the original.

The reason for only storing the set modifier without the angle brackets in the variable definition is to allow for the flexibility to include additional modifiers in the end expression. For example, we can extend the set expression as follows:

Sum({$<$(vSetYTD), [Carrier's Operating Region] = {'Latin America'}>} [# Departures Performed])

By adding expression-specific set modifiers, all of the other expressions using the base set variable remain un affected.

Further more, we can define all of the set expressions used for period comparisons so that they are ready to be used when required from any given expression:

Point-In-Time-Reporting-Figure

Once we have defined all of the preceding variables, the creation of new expressions for period comparisons will be a very straight forward process.

Set expressions with parameters

We will expand the concept a bit further by incorporating what we discussed in the previous chapter about variables with parameters and apply it to a set expression. Let's follow these steps:

  1. Open the Variable Overview... window (Ctrl + Alt + V) and create a new variable. Name it vSetPreviousNMonth.
  2. The variable definition will be:

Look closely and you'll notice that the new variable is almost the same as the one defined previously, called vSetPreviousMonth. The difference between both variables is that we are now inserting a parameter into the PeriodID value definition. This parameter is represented by $1, and will allow us to use the same set variable to obtain the corresponding record set either for the previous month (when the parameter's value is 1), for two months ago (when the parameter's value is 2), and so forth, all using the same set variable.

To use the preceding variable in an expression, start by creating a new chart in the form of a straight table with Carrier Group as the dimension and the following three expressions:

  1. The first expression, which we will label as Current Month Flights, will be:
    Sum({$<$(vSetPreviousNMonth(0))>} [# Departures Performed])
  2. The second expression, which we will label as Flights Previous Month, will be:
    Sum({$<$(vSetPreviousNMonth(1))>} [# Departures Performed])
  3. The third expression, which will be label as Flights Two Months Ago, will be:
    Sum({$<$(vSetPreviousNMonth(2))>} [# Departures Performed])

The only difference among the preceding expressions, apart from the label, is the parameter's value inserted into the variable.

After properly formatting the expressions' values and the chart's presentation, we will have the following chart:
Flights per Carrier Group
Flights per Carrier Group

Awesome!

We can even take this one step further. We previously said that period comparisons are widely used in almost any QlikView document, but what we've done with the preceding procedure is define the variables in one document. What about all of the other documents? Do we need to create each of these variables over and over again for each of our QlikView documents? Well, let's discuss an alternative.

Portable set expressions

Now that set expressions are handled via variables, we can automate the process of creating these variables, instead of doing it all manually. We will use an include statement to create the variables during script execution and by using a text file shared across different QlikView documents.

To begin, we will create a text file, containing the code used to define each variable. The contents of our text file will be:

When defining our set variables in the script, we must be very cautious because of the presence of the dollar sign inside the variable's text. If we were to use the dollar sign directly inside the variable's definition, QlikView would interpret it as something to be expanded in the process, causing the load script to fail. Therefore, we have avoided the insertion of this symbol and used a string function instead. Chr(36) results in the dollar sign, as it is the ASCII character used to represent the symbol. The resulting string is concatenated to the rest when the variable is defined.

We have also inserted line breaks with the Chr(10) function.

We will name the text file as SetVariables.txt and store it in the same location as the Airline Operations document.

[rlTX. A copy of the finished SetVariables.txt has been

included into the Airline OperationsApps folder.

Now that we have the text file, we will include it into our script using the following statement:

$(Include=SetVariables.txt)

When we run the script, the corresponding variables will be created and are ready to be used on the chart's expressions. The same statement could be used on any QlikView document to make them execute the same SetVariables.txt script and share the same set variables across them.

Set variables and the Master Calendar

As the set variables created with the preceding script are based on field names, all of those fields must exist (preferably in a Master Calendar table) in the document that uses the variables, otherwise they will not work as expected. Therefore, we advise you to integrate both the set variables and the Master Calendar scripts into a single, generic script stored in a text file to be called from any QlikView document, ensuring consistency and functionality.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Qlik View Topics