When Totals Don’t Work

Qlikview (QV) provides for three types of totals in straight tables and pivot tables:

• None
• The expression calculated at the total level
• Aggregate function (sum, average, minimum etc) of the table rows

These options cover most cases, but sometimes you have a table where none of these options is appropriate, or provides the correct result. As an example, consider a table of absolute variances, such as budget amount vs selling amount, on a table dimensioned by product class. The expression for the variance and % variance would be:

[AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount))

[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)


These expressions yield different results for a different degree of dimensioning, so the sum of the absolute variances per ProductClass is not the same as the absolute variance at the total level. Therefore, the AbsoluteVariance column needs to be totalled using the Sum of rows option. For the %AbsoluteVariance, however, the expression at the total level does not work for the same reason, and the summing of the rows is arithmetically meaningless. In this example, a total is required for both of these columns.

So, how do we construct an expression that will produce the correct %AbsoluteVariance for each row and for the total?

We have the expression for the rows:

[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)

For the total, we need an expression like:

[%AbsoluteVariance] = Sum(Rows of AbsoluteVariance) / Sum(BudgetAmount)

Assuming that ProductClass was the only dimension on the table, the Sum(Rows of AbsoluteVariance) can be calculated as follows:

Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass))

So, how do we differentiate between the two expressions?

This is where the QV Dimensionality() function comes in. In our case, Dimensionality() will return zero when calculated on the total row, and greater than zero elsewhere. Check the Qlikview Reference Manual for more information on the function.

Our final expression becomes:

If(Dimensionality() = 0,
// total row expression
Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass)) / Sum(BudgetAmount),
// other row expression
Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount))

QED

Unzip the download here and play around with a sample QVW model and the Excel source file.

A Novel Bookmark Display

This post arose after a client asked for a view of document bookmarks that was similar to a horizontally arranged list box selector. The QV model had a number of states which could be selected using these bookmarks. He wanted his staff to be able to see the selected. The solution looked something like this:

I achieved this as follows:


Step 1: Create the document bookmarks in the normal manner.

Step 2: Open the bookmarks dialog (Bookmarks | More) to get the bookmark Ids for each bookmark. Then uncheck the checkboxes in the “+” column.

Step 3: Create an inline table in the load script with the bookmark names and IDs – something like this:

LOAD * INLINE
[
   BMName, BMId
   Stock on hand, BM32
   Reclaim pending, BM30
   Legal hold, BM26
   Write offs, BM33
   Phyical stock, BM29
   Saleable stock, BM31
];

Step 4: Create a list box containing BMName, and arrange horizontally. You can also sort the list box by load order, if appropriate.

Step 5: Add the BMName selection to each bookmark, by selecting the bookmark (from a bookmark control, or the menu. Then select the corresponding BMName value. Finally, click Bookmarks | Replace bookmark to update the bookmark with the BMName selection. I used an open Current Selections box to verify the correct behaviour.

Step 6: (Almost there!) Go to Settings | Document Properties | Triggers and add an On Select trigger on the field BMName. Add a Clear All action, followed by an Apply Bookmark action. For the Bookmark ID for the latter, enter “=BMId” (without the quotes!).

That’s it. Now selecting a value in the “selector bar” for the bookmarks will apply the bookmark and show the currently applied bookmark. Clicking the selected value will clear the bookmark (ie normal list box behaviour).

If you find this useful, or would have any comments on this technique, please post a comment.

Qlikview Working Day Functions 3

This post concludes the short series on how to use the working day functions in Qlikview. (Start from the beginning)

Putting it together (IsWorkDay subroutine)

The code below is a subroutine, callable from the load script, that evaluates a date and return true if it is a work day, and false if it is not.

Parameters:

• testDate (in, date) - the date to test
• rval (out, true/false) - true if the date is a work date, false otherwise.

Variable vPublicHolidays is a string containing a comma separated list of public holidays. See the earlier post for more information.

Sub IsWorkDay(rval, testDate)
Let zTest = NetWorkDays(MonthStart(testDate), testDate,
$(vPublicHolidays));

    // If the day is a non-working day, and there are no
    // working days before it in the current month,
// NetWorkDays will return 0 
If zTest = 0 Then
rval = false();

    // If the day is a working day, and/or there are working
    // days before it, NetWorkDays will return > 0
Else
       // If the date is the first day of month then it must
  // be a work day
If DayStart(testDate) = MonthStart(testDate) Then
  rval = true();
       // Else compare with NetWorkDay for yesterday. If 
        // different, then the date is a work day
Else
  rval = If((zTest <> NetWorkDays(MonthStart(testDate),
      testDate-1, $(vPublicHolidays))),
      true(), false());
End If
End If
End Sub;

Put this script on a separate tab on your load script, or in an include file. Then call it like this:

Let vTheDate = Date#('2010/09/22', 'YYYY/MM/DD');
Call IsWorkDay(vTheDate, rval);
If rval Then
... do something if it is a work date
Else
... do something if it is not a work date
End If

The series start page is here.

Qlikview Working Day Functions 2

This post continues the short series on how to use the working day functions in Qlikview. (Start from the beginning)

LastWorkDate([start date], n)

This function calculates the date of the nth working day after the start date. The start date is work day number 1 if it is a working day and if the start date is a non-working day, the function will return the next working day for n = 1.

Calculate the date of the nth working day of this month:

=LastWorkDate(MonthStart(Today()), n)

eg LastWorkDate(MonthStart('2010/09/15'), 11) = '2010/09/15', so working day 11 of the month is 15 September 2010.

FirstWorkDate([start date], n)

This function calculates the date of the nth working day before the start date. The start date is work day number 1 if it is a working day and if the start date is a non-working day, the function will return the previous working day for n = 1.

Calculate the date of the nth last working day of this month:

=FirstWorkDate(MonthEnd(Today()), n)

eg FirstWorkDate(MonthEnd('2010/09/15'), 2) = '2010/09/29', so the 2nd last working day of September is the 29th.

Long Form

Both these functions have an optional long form similar to NetWorkDays, namely a list of non-working days to take into account in the calculation. See the earlier post on NetWorkDays for more information.

See the next post on putting it all together.

Qlikview Working Day Functions 1

This post begins a short series on how to use the working day functions in Qlikview.

NetWorkDays (short form)

Calculate the working day number for today:

=NetWorkDays(MonthStart(Today()), Today())

eg NetworkDays(MonthStart('2010/09/15'), '2010/09/15') = 11, so the 15^th is the 11^th working day of September 2010.

Calculate the number of working days in the current month:

=NetWorkDays(MonthStart(Today()), MonthEnd(Today()))

The above examples are the short form of NetWorkDays, which consider working days to be Monday to Friday.

One limitation for people living in regions with calendars different to the standard Western calendar is that there does not appear to be a way to make the basis for the working day calculations to be anything other than Monday to Friday.

NetWorkDays (long form)

The long form of NetWorkDays allows you to take public holidays into consideration. This format adds an arbitrary number of dates to the parameter list which will be considered non-working days, such as:

=NetWorkDays(MonthStart(Today()), Today(), '2010/09/24', '2010/09/25')

This will treat the 24^th and 27^th of September 2010 as non-working days. Note that 25 September 2010 is a Saturday, so is already a non-working day. This is correctly ignored by the NetWorkDay() function.

Using the long form

I have usually used the long form by reading public holidays from a spreadsheet (any data source will do), and concatenating the results into a variable. The script is:

tmpHoliday:
LOAD Date([DATE], 'yyyy/MM/dd') as Date
FROM [..\QVDATA\Public Holidays.xlsx]
(ooxml, embedded labels, table is Sheet1);

tmpConcat:
LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates
RESIDENT tmpHoliday;

Let vPublicHolidays = fieldvalue('HolidayDates',1);

DROP TABLE tmpHoliday;
DROP TABLE tmpConcat;

Now I can use vPublicHolidays like this:

=NetWorkDays(MonthStart(Today()), Today(), $(vPublicHolidays))

Next article on FirstWorkDate and LastWorkDate

Searching QlikCommunity

Many of you will know that the forums of QlikCommunity are extemely helpful if you have a QV problem.

Heres a useful tip that I picked up elsewhere:

You can use google to search QlikCommunity. For example, enter this as the Google search string:

Rolling 12 Months site:qlikview.com

Copying colours

Did you know that you can copy colours from one colour block to another in QV?

Qlikview colour block

Right click on the colour block for a context menu with the following options:

1. Copy - copy the colour information to the clipboard. This information includes the colour gradient information.
2. Paste All - paste the complete colour information from the clipboard.
   
3. Paste Base Color - paste the base colour only.
   
4. Paste Second Color - paste the second colour (if any).
   
5. Paste Gradients - paste the gradient information (type and shading style).