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:

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

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.

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:

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.

If(Right(Max(Period) – 1, 2) = '00', (Max(Year) – 1) & '12', Max(Period – 1))

(Year(Temp_Date) – 1)  * 12 + Num(Month(Temp_Date))

Max(PeriodID) – 1

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

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.

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:

[Master Calendar]:
Load
   *,
   AutoNumber(Period, 'PeriodID') as [PeriodID],
   AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID]
   ;
LOAD DISTINCT
   Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
   Year(Temp_Date) as [Year],
   Month(Temp_Date) as [Month],
   Date(Temp_Date, 'YYYY-MM') as [Year - Month],
   'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
   ;
LOAD DISTINCT
   MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Save and reload the script to apply the changes.

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:

Sum({$<PeriodID = {"<=$(=Max(PeriodID))"},
              Year = {$(=Max(Year))},
              Quarter = ,
              Month = ,
              Period = >} [# Departures Performed])

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 unaffected.

Furthermore, 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:

PeriodID = {"<=$(=Max(PeriodID))"},
 Year = {$(=Max(Year))},
 Quarter = ,
 Month = ,
 Period = 

PeriodID = {"<=$(=Max(PeriodID))"},
QuarterID = {$(=Max(QuarterID))},
 Year = ,
 Quarter = ,
 Month = ,
 Period = 

PeriodID = {$(=Max(PeriodID))},
 Year = ,
 Quarter = ,
 Month = ,
 Period = 

PeriodID = {$(=Max(PeriodID) – 1)},
 Year = ,
 Quarter = ,
 Month = ,
 Period = 

QuarterID = {$(=Max(QuarterID) – 1)},
 Year = ,
 Quarter = ,
 Month = ,
 Period = 

PeriodID = {$(=Max(PeriodID) – 12)},
 Year = ,
 Quarter = ,
 Month = ,
 Period = 

QuarterID = {$(=Max(QuarterID) – 4)},
 Year = ,
 Quarter = ,
 Month = ,
Period = 

PeriodID = {"<=$(=Max(PeriodID) – 12)"},
 Year = {$(=Max(Year) – 1)},
 Quarter = ,
 Month = ,
 Period = 

PeriodID = {">=$(=Max(PeriodID)-11)<=$(=Max(PeriodID))"},
 Year = ,
 Quarter = ,
 Month = ,
 Period = 

Once we have defined all of the preceding variables, the creation of new expressions for period comparisons will be a very straightforward 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:

   PeriodID = {$(=Max(PeriodID) – $1)},
   Year = ,
   Quarter = ,
   Month = ,
   Period =

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:

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.

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:

Let vSetYTD = 'PeriodID = {"<=' &Chr(36) & '(=Max(PeriodID))"},' &Chr(10) &
'Year = {' &Chr(36) & '(=Max(Year))},' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetQTD = 'PeriodID = {"<=' &Chr(36) & '(=Max(PeriodID))"},' &Chr(10) &
'QuarterID = {' &Chr(36) & '(=Max(QuarterID))},' &Chr(10) &
'Year = ,' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetMTD = 'PeriodID = {' &Chr(36) & '(=Max(PeriodID))},' &Chr(10) &
'Year = ,' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetPreviousMonth = 'PeriodID = {' &Chr(36) & '(=Max(PeriodID) - 1)},' &Chr(10) &
'Year = ,' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetPreviousQuarter = 'QuarterID = {' &Chr(36) & '(=Max(QuarterID) - 1)},' &Chr(10) &
'Year = ,' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetLYMTD = 'PeriodID = {' &Chr(36) & '(=Max(PeriodID) - 12)},' &Chr(10) &
'Year = {' &Chr(36) & '(=Max(Year)-1)},' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetLYQTD = 'QuarterID = {' &Chr(36) & '(=Max(QuarterID) - 4)},' &Chr(10) &
'Year = ,' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetLYYTD = 'PeriodID = {"<=' &Chr(36) & '(=Max(PeriodID) - 12)"},' &Chr(10) &
'Year = {' &Chr(36) & '(=Max(Year)-1)},' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

Let vSetRolling12 = 'PeriodID = {">=' &Chr(36) & '(=Max(PeriodID) - 11)<=' &Chr(36) & '(=Max(PeriodID))"},' &Chr(10) &
'Year = ,' &Chr(10) &
'Quarter = ,' &Chr(10) &
'Period = ,' &Chr(10) &
'Month = ';

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.

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.