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

Design Mode

One of the criticisms of Qlikview that I have heard raised is that there is no design mode - in other words the same environment is used to design models and to use them.

However, QV does have a design mode - of sorts. I didn't know this until recently and stmbled across this indirectly in another blog (sorry, can't remember whose).

To activate design mode, turn on the design grid. On the menu click View | Design Grid or press Ctrl-G to toggle on and off.

OK, so what do I get in this design mode, apart from the grid of course? Well, now I have sizing/moving handles on sheet objects, and some additional context menu items (z-ordering sheet objects, column width aids, interactive sorting and indivdual cell properties)

I said "of sorts" above because it is not a pure design mode like other applications. You can still interact with the model as a normal user, mixed up with the design stuff.

Selection and month views

If you set up a simple month view chart such as a chart of the sum of transaction amounts by day, for example:

• using a dimension such as Day(TransDate)
• using an expression like Sum(TransAmount)

Now, if the user views this chart with no year and month selected, they will get the sum of amounts for that day of the month for all months loaded into the model. Not very useful.

Another approach is to make the chart display summed amounts for the current month. If a selection is made, then display summed amounts for the selected month/year.

This is how I do this:

1. Create TransYear and TransMonth fields on loading
2. Put listboxes with these two fields on the sheet
3. Build the chart
4. Filter the chart expression(s) with the following date filter (added filter highlighted):

Sum(If(TransDate >= MonthStart(Max(Total TransDate)) AND TransDate <= MonthEnd(Max(Total TransDate)), TransAmount)


5. Now, if nothing is selected, the TransDate range will be in the current month (or at least the last month in which a transaction was recorded - in most cases this will be the same date).

If you select a month only, then Max(TransDate) will be the month in the most recent year that contains data for that month. For example, in April: selecting Jan, Feb, Mar or Apr will display data for that month in the current year. Selecting any other month would display data for that month in the previous year.

If you select a year only, then Max(TransDate) will be the last day of that year with transactions. If that is the current year, then that will be today (or yesterday).

Any other selection (of TransDate, TransMonth, TransYear) will cause the month with of the last possible TransDate to be displayed.

You can, of course, do the filtering with a set expression if you prefer. I find this syntax more intuitive, but the final choice should depend on performance. Maybe you would like to convert this to a set expression in a comment below?

Tooltip for buttons

Did you know that QV models can display a tooltip for buttons?

Enter the tooltip text in the Help Text box on the Caption tab of the button properties. If the user hovers their mouse over the button, the text will pop up in tooltip.

This also works for text boxes.

Set Analysis Problem

The problem

To display data in a way that ignores selections, you can use a set expression {1} in any aggregation functions. This is useful for a dashboard page which displays (for example) the last 30 days data. The chart expression could be something like:
=Sum({1} Distinct If(TranDate>=today()-30, TranValue))

I have a chart to display data which must ignore all selections, except one (on a field named ForcedLoadingState, which could contain 'Yes' or 'No'). The problem was how to create a set expression which would ignore all selections except on ForcedLoadingState.

The solution

1. Create a variable (eg vForcedLoadingState).

2. Use the set expression:
{1<ForcedLoadingState={$(=vForcedLoadingState)}>}

3. Define the variable as:
=If(Len(ForcedLoadingState)=0,'Yes,No',ForcedLoadingState)

Comments

This allows the user to see the data unfiltered by ForcedLoadingState (ie no selection) or for either state of ForcedLoadingState. When ForcedLoadingState is unfiltered, the variable will pass both Yes and No as valid options to the set expression. The '1' in the set expression overrides all other selections.

If(Len(field) = 0,..) is equivalent to If(IsNull(field),..), but I have found the Len approach more reliable. Maybe that's just me :-)

If there are more than two valid states, then the variable expression will need to include all the options in a comma separated list. This will work as long as there is a small number of possible options.

Qlikview Log Viewer

I have developed a simple Qlikview model that reads the log files for the Qlikview models in the same folder and shows a simple status dashboard showing the last reload date, success or failure of the reload, the number of reload errors if any and the relative time that the reload took. See image below:


To use, just copy the qvw file into the same folder and enable logging on the models that you wish to monitor, and set this model to reload periodically. If you have a series of models reloading overnight, just ensure that this model runs last.

Clicking on one of the model names displays the log data for the last reload of that model.

The load script is:

SET ThousandSep=' ';
SET DecimalSep='.';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep='.';
SET MoneyFormat='R # ##0.00;R-# ##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD hh:mm:ss';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

For each File in filelist ('*.log')

tmpLOG:
REPLACE LOAD
Date#(@1:9, 'MM/dd/yyyy') as Date,
Time#(@10:17, 'hh:mm:ss') as Time,
@20:24 as SourceLine,
RecNo() AS LogLine,
PurgeChar(Trim(@25:n), chr(9)) as Detail,
FileName() AS LogFile,
FileTime('$(File)') AS LogDate
FROM '$(File)'
(fix, codepage is 1252);

Let zStartts = TimeStamp#(Date(Peek('Date', 0), 'yyyy/MM/dd') & ' ' & Peek('Time', 0), TimestampFormat);
Let zEndts = TimeStamp#(Date(Peek('Date', -1), 'yyyy/MM/dd') & ' ' & Peek('Time', -1), TimestampFormat);
Let zInterval = Interval(zEndts - zStartts, 'hh:mm:ss');

Let zFileName = Peek('LogFile');

LOG:
LOAD *,
'$(zInterval)' AS ElapsedTime
RESIDENT tmpLOG
WHERE Len(Date) > 0 AND Len(Detail) > 0;


ERRORLOG:
LOAD
LogFile,
LogDate,
Count(*) AS ErrorCount
RESIDENT tmpLOG
WHERE Detail LIKE 'Error:*'
GROUP BY LogFile, LogDate;

DROP TABLE tmpLOG;

Next File

You may need to tweak the data formats to suit your system.

Load the model here. You will require a licensed version of Qlikview to open this model. If you are using the community download version of QV, you can roll your own by creating a new model, copying and pasting the script above, and importing this XML sheet layout.

The standard free software disclaimers apply.