The right negative

I recently had a situation where a customer was not happy with the values returned in a budget variance column.

Simple calculation for variance - (Spend - Budget)/Budget

However, in some of their profit centers there was no budget but there was spend! I.e.: (Spend)/0.

Rather than just display a "-" or 0, they wanted this to display as -100%, to really flag it up.

Following on from my previous post about the RangeMax function in QlikView, I thought about it but then, deciding that wouldn't work for me here, I decided on the Alt function.

The Alt function is quite useful because, like RangeMax, you give it a series of Expressions but what it does is returns you the first one that results in a valid number. So, if I use:

Alt((Spend-Budget)/Budget, -1)

When the variance percentage is Null (when Budget is 0), then the value returned will be -1 (-100%). Exactly what I needed.

Alt can also be useful where you have mixed date formats in a date stream - using multiple Date# functions in an Alt and one of them should give you a number.

Keeping it positive

I had a request that when the result of a particular expression was negative, then it should just display as zero (it was a measure of how far out of compliance the result was - a negative result meant that they were in compliance so zero was appropriate).

So I could do a simple:

if(myExpression > 0, myExpression, 0)

That is OK if the expression is simple, but if the expression is more complex then it becomes unwieldy and long and hence difficult to maintain.

A much more elegant way is to use RangeMax like this:

RangeMax(0, myExpression)

Very simple, if my expression dips into the negative then the max value in the range becomes 0. Exactly what I want to achieve and much easier to maintain.

Lies, damned lies...and then there is statistics

I spotted this in a Microstrategy publication which showed the "TCO" for Microstrategy over other operators:



This is a fairly typical use of a flawed statistic to prove a point.

You see, if you have a 10 user QlikView system, you have 1 IT person looking after it - the same guy that is looking after Windows, Email, buying coffee, etc. The same guy can probably look after QlikView as the company grows. So that is a 1 IT person for 10. Push that out to 1000 users and it become a "statistic" of 100 IT people per 1000.

Given that, until recently, QlikView has been playing more in the SME sector, then these numbers are going to be more than less typical. Therefore it probably says something that the "statistic" is only 21.9/1000.

At the other end, of course Microstrategy are best because of only 4.9 IT people per 1000. There is no real qualatitive data behind this. Microstrategy play in the Enterprise sector where they will regularly sell large CAL deals as well as open ended deals - we have no idea how many, exactly, users there are.

Its probably good to know that while your 21.9 QlikView IT people are busy looking after the Email server and making the coffee, your 4.9 Microstrategy guys are busy doing Microstrategy and nothing else.

Using Alerts in QlikView

Alerts are quite easy to set up. With a few tricks, they can become quite powerful.

Let me start with a very simple data set:

CountrySales:
LOAD * INLINE [
Country, Sales
England, 400
Ireland, 300
Scotland, 200
Wales, 100
];

Now, I want to create an Alert when there are any countries that have Sales > 200.

To create an alert, I need to be able to create an expression that will return a true value. I could use a simple aggr expression like:

=Sum(Aggr(If(Sum(Sales)>1, 1), Country)) > 0

This will work. However, it means that we are going to be limited to what information we can include in the Alert message. We can include the number of Countries, but we couldn't, for example, say which ones they were.

A more powerful way is to combine Advanced Search, Bookmarks and Alerts.

If I create an Advanced Search against the Country field such as:

=Sum(Sales)>200

Then I save this search as a Bookmark. Now, I can create an Alert with this Bookmark used where the Alert is simply:

=Count(Distinct Country) > 0

And the Alert message could be something like:

='Number of countries exceeding 200 = ' & Count(Distinct Country) & chr(13) &
'Countries: ' & Concat(Country, ', ')

Of course, I can bring any other information into this message that is related to the countries that match the Bookmark.

Now, if I set-up the "Mail" options under User Preferences, I can have this document email an alert to someone.

If I turn on the alert to use "Batch Mode", this can happen on the server when the document is reloaded.

AND Mode in list boxes

By default, multiple selections in list boxes are "OR" mode. For most applications this is exactly what we need.

However, there are situations, such as that requested by Wolfgang Praschnig on QlikCommunity this week:
http://community.qlikview.com/forums/p/22224/84898.aspx.

Wolfgang wants to be able to see customers who have bought one product AND bought another product. The default action of QlikView would be to show customers who had bought one product or the other.

To enable "AND mode" in a list box, there are a couple of rules that we need to follow:

1. The field in the list box must be contained in a table that has only 2 fields.

2. The other field in the table is the association (or ID) field. This field is only contained in this table.

3. The table must have been loaded with the "DISTINCT" keyword.

This can sometimes be tricky to achieve, so hopefully an example will help demonstrate.

Lets say that we have 3 tables:

Orders
OrderID, CustomerID, OrderDate

OrderDetails
OrderID, LineNo, ProductID, Quantity, Price

Product
ProductID, ProductName

What we want to do is connect the product name to the Orders table in a way that we can employ AND mode.

This means that we will need to create a table that has the OrderID (to associate to Orders) and a ProductName field (which we will call "ProductNameAND") that we can put in our List Box with AND mode on.

So, first we need to create a table with the OrderID and ProductID from the OrderDetails table:

ProductAND:
LOAD DISTINCT
OrderID, ProductID As PID
RESIDENT
OrderDetails;

Note that I have aliased the ProductID field as PID - this is because I am going to drop it in a minute. Also note the DISTINCT keyword.

Now, bring in the Product name:

LEFT JOIN (ProductAND)
LOAD DISTINCT
ProductID As PID, ProductName As ProductNameAND
RESIDENT
Product;

Finally, drop the PID field so we are left with only the 2 fields as required.

DROP FIELD PID;

Now, we can add the ProductNameAND field in a List Box and the AND mode will be available.

Interesting feature here. When you click on an item in an AND mode box, an "&" will appear to the left of it. Pressing CTRL and clicking on additional "possible" (in white) items will add further "&"s beside those items. However, if you click on an "excluded" item or you click and hold the mouse down for a second on "possible" items, it turns to a "!" instead - i.e. "NOT". This can be really powerful for queries.

SR2 released and looking good

Well SR2 for QlikView 9 was released last week.

So far, so good. Looks like all my reported bugs have been fixed.

My opinion is that this Service Release is really what SR1 should have been if it hadn't been rushed forward to get some of the bad bugs from the initial release fixed.

With all of the great things that are in v9, we can now get on with the business of implementing it freely without having to worry about bugs that just shouldn't have been there.

Hurry over to QlikCommunity - http://community.qlikview.com - to get your copy.

Retrieving Database Values from within a document

There may be many reasons why you might want to get a value from outside of QlikView at any time, without having to reload your document (even partially).

The obvious method is to use macros to retrieve the data using ADO calls.

However, there is a function in QlikView that can do this - but only to retrieve one value (e.g. a record count). The function is called SqlValue.

The function takes 3 parameters:

- Connection
- Query
- Connection Type

Connection Type is only one of 'OLEDB' or 'ODBC'. If you leave it out, ODBC is assumed.

The Query is a string that should (although not absolutely required) return one row and one value. If it returns more, only the first value in the first row is returned.

The Connection is straightforward if using ODBC because you just use the predefined ODBC connection name. For OLEDB, you need to pass an OLEDB connection string. Now, this can be messy (as they can get quite long and include single quotes which can throw you a bit. For me, the easiest way is to set up a variable with the Connection string in it (perhaps it could be stored in the registry and retrieved using GetRegistryString!)

Once you have it, then you just call like this:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders', 'OLEDB'))

Note that I have used the Num function here to cast the return value as a numeric. The SqlValue function returns a dual so, if you want to display it, you need to cast as numeric or it will display as blank (no Text value in the Dual).

The query that you pass can be as complex as the target database supports and can use values from your QlikView document:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders Where CustomerID in (' & Concat(Distinct CustomerID, ',') & ')', 'OLEDB'))

Obviously, and this is flagged in the help file, if you have queries running in your document, they can slow things down while waiting for the database query to return.

Removing Fields with a Wildcard

One of my clients asked an interesting question. They will have a standard set of QVDs to load data from that may be used in various documents. In each document, they will also have a mapping table to mass rename the fields that they need for that particular document.

Now, with their dataset, they will be left with a load of fields that start with the word "Custom_" - sometimes some of these fields will be aliased, sometimes they will not be used. If they are not used, then they will want to drop the fields from the document (to keep things nice).

Unfortunately, QlikView does not accept a command like

DROP FIELDS "Custom*";

So, we can use a couple of functions that are available in the script to do this for us. Here is a script sample:

MyTable:
LOAD * INLINE [
F1, F2, F3, F4
1, 2, 3, 4
];

Rename Field F1 to NewField;
Rename Field F3 to NewField2;

Let i = 1;

Do While i <= NoOfFields('MyTable')

Trace Getting Field $(i) From MyTable;

Let FieldName = FieldName($(i), 'MyTable');

Trace FieldName = $(FieldName);

Let Command = If('$(FieldName)' Like 'F*', 'Drop Field $(FieldName);', '');

Trace Command = $(Command);

$(Command)

Let i = $(i) + If('$(Command)' = '', 1, 0);

Loop

So, I load 4 fields, rename 2 of them, then loop through all the fields looking for a match ('F*') for the original names and create a command to execute in the script. (This is also a good example of using a dynamically created command in a script!)

The reload window should look like this:

MyTable << INL47EA 1 lines fetched
Getting Field 1 From MyTable
FieldName = NewField
Command =
Getting Field 2 From MyTable
FieldName = F2
Command = Drop Field F2
Getting Field 2 From MyTable
FieldName = NewField2
Command =
Getting Field 3 From MyTable
FieldName = F4
Command = Drop Field F4

Note that you need to use a While statement here not a For loop. This is because once you drop a field, say Field #2, the next field becomes that field number - Field #3 becomes Field #2 - so you will end up skipping fields. The While statement prevents this.

Using Sets to avoid Key Tables

This may not appeal to purists, but it might be a good way to get out of a fix when you are in a hurry to get something in front of people.

Let's create a QlikView Script:

Sales:
Load * INLINE [
Date, Item, Store, Quantity, Price, NetValue
1/1/2009, 1, 1, 1, 2.00, 2.00
1/1/2009, 1, 2, 2, 2.00, 4.00
1/1/2009, 2, 1, 1, 2.00, 2.00
1/2/2009, 2, 2, 1, 3.00, 3.00
1/2/2009, 1, 1, 3, 3.00, 9.00
1/2/2009, 2, 2, 1, 3.00, 3.00
];

Product:
Load * INLINE [
Item, Description
1, Product A
2, Product B
];

Store:
Load * INLINE [
Store, Name
1, Store 1A
2, Store 2B
];

Now, this will create a nicely connected QlikView document. Now, I am going to introduce a Stock table that tells us the stock levels for the products in each store:

Stock:
Load * INLINE [
Store, Item, OnHand
1, 1, 55
1, 2, 33
2, 1, 23
2, 2, 12
];

This will create a nice Synthetic Key!



Now, the normal approach to avoiding the Synthetic Key is to start creating a Key table to nicely join all the tables. This, of course, uses lots of Joins and Concatenation and Resident loads (which are slow!!!). All of this means that your document loading takes a lot longer - losing any QVD optimisation that you might have.

Here is my solution to resolve this Synthetic Key:

Stock:
Load * INLINE [
StockStore, Item, OnHand
1, 1, 55
1, 2, 33
2, 1, 23
2, 2, 12
];

"Hey! You can't do that", I hear you all shouting. You have broken your links! How can you see the correct stock levels? If someone selects a Store, then the value of the stock in the chart won't change! If you chart it now, you will just get the same value because there is no association.

And this, of course, is true. If I, for example, create a Straight Table with a dimension of "Description" (the Product Name) and use the expression:

Sum(OnHand)

I will get the 78 for Product A and 45 for Product B. Now, if I select Store 1A, the values will not change - which is incorrect.

But how about if I use this expression:

Sum({<StockStore = P({$} Store)>} OnHand)

Now the values do change if I make selections on Store.

This is using the new "P" option in Set Analysis to allow me to make a set of the relative values. So, my "P" returns a set of the selected Stores in this document and then I am using that to filter the StockStore value.

One downside - and I would love to hear from someone who can overcome it - is that it doesn't work if the dimension is Store instead of Product. I need to work on that.

Microsoft Gemini

I just rewatched this video again: http://www.youtube.com/watch?v=MzgMMO-P9F0 - A preview of Microsoft Gemini in Excel 2010.

Why is it that when he says, 'we call them "Slicers"', I can hear Dr. Evil saying 'I call it a "LASER"' ;-)

Well, Gemini is now available as a Community Preview. You can get access to the Gemini CTP if you are a member of the Office 2010 Tech Preview by visiting http://www.microsoft.com/officebusiness/office2010/Default.aspx?vid=Gemini (if you go to that link, click on "Videos", there is a really bad "Project Gemini for Excel 2010" video that you can watch).

Is Gemini a "QlikView Killer"? I am not sure. All I can say is that my experience tells me that QlikView rocks compared to this demo. My experience also tells me that Microsoft are into marathon's, not sprints.

Avoid Resident for large data sets

As part of your training, you were probably taught to us Resident loads to re-use data that has already been loaded. I have used this and taught this for several years.

However, my experience recently with larger data sets is that resident load is very slow!

A much better choice is to re-use a QVD if you have one. In fact, if you write the in memory table to a QVD (using STORE), and then use that QVD for your next load, it is still faster than using Resident!

Custom popup labels on bar chart

The default pop-up for a QlikView bar chart is not always the most friendly. It shows the values for dimensions and expressions in the form:


  Dimension 1 = Value
  Dimension 2 = Value
  Expression = Value

Now, we may not like this popup and wish to display the values differently or add different formats. We may even want to include additional information.

Well, you can. There are a couple of steps:

Step 1
For your existing expression, make sure that the "Text as Popup" option is turned off.

Step 2
Add a new Expression that calculates out the correct string that you want to display in your popop with all the correct formatting. For this expression, turn on that "Text as popup" option. But we don't want the chart trying to display a real bar for this expression so take off the "Bar" option (the icon for the expression will change from the Bar icon to a text bubble icon).

Step 3
We need to stop the default popup - on the Presentation tab, turn off the "Pop-up labels" option.

Job done.

If you are on QlikCommunity (and if you aren't, why on earth not???) then you can grab a demo of this created by me from:
http://community.qlikview.com/media/p/78614.aspx

More on Dual

I posted recently about the very useful Dual function (to store a numeric value and a text value - e.g. for Months).

Another good application of this function is in a chart calculation. If your calculation includes a dual, e.g.:

Dual(Country & ' (' & Num(Sum(Sales)/Sum(Total Sales),'0.00%') & ')', Sum(Sales))

Then you will have both a Number and a Text value - either or both can be displayed on the chart using the "Text on Datapoint" and/or the "Numbers on Datapoint" options in the Express Tab of the chart.

One use of this is to display 2 disfferent calculations on, say, a pie chart. In the above example, the Pie chart could be set to display both the total values and the percentages - something that is not achievable any other way.

Keeping a Trace on things

I love QlikView. I love the way that I find new things all the time.

Occasionally, when I am very bored, I sometimes have a look in the help file for things that I am not familiar with. That is how I learned about Trace.

Trace is very cool in that it allows you to both comment your code and have those comments appear in the Script Execution dialog. It also supports variable use.

So, I could have a script like this:

For i = 1 to 10

Info:
Load
Information
From File$(i).qvd (QVD);

Next

In the past, I may not have been exactly sure where in the look I am without having to physically count the entries in the Script Execution dialog. Now, I can add a trace into the loop:

Trace Loading from file: File$(i).qvd;

And it will appear in the Script Execution like:

Loading from file: File1.qvd

What's more, it also appears in the script execution log file (you have to turn that on in the Document Properties - General - Generate Log File).

I wonder what interesting things that you might find in the help file?

Using Dual

QlikView has a really cool data type called "Dual". This type allows you to mix text and numeric values which can be very useful.

For example, imagine that you had some pieces of text, say:

Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep

And you would prefer to have them sorted in another order, say:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

Then we could load this something like:

Load
Dual(Name, Num) As Month;
LOAD * INLINE [
Num, Name
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec
];

And the Month field would be sorted in the correct order.

If we need to get the component values spearately out of a Dual value, we can use Num and Text:

Text(Month) & ' (' & Num(Month) & ')'

Might give us a result of:

Apr (4)

Change the Command Centre port in v8.5

Thought that I might document this here while I still have an 8.5 server to play with.

Often, other applications will grab the ports that QVP processes use on your server (e.g. McAfee AV will often use 8081). To change the QVP ports there are a couple of places that you need to make changes.

In Program Files\Qlikview\ControlPanel, edit web.config and change the entry:

< add key="CommandCenterURL" value="http://localhost:8081/qtcc.asmx"/ >

to

< add key="CommandCenterURL" value="http://localhost:8765/qtcc.asmx"/ >

(I am using 8765 as the example port to change to).

In Program Files\Qlikview\CommandCenterService, edit QVPublisherCommandCenterService.exe.config and change the entry

< add key="WebservicePort" value="8081" / >

to

< add key="WebservicePort" value="8765" / >

Stop and start the services and you should be OK. You can always test the service on the server by calling the web service:

http://localhost:8765/qtcc.asmx

It should respond:

CommandCenterService v 2.0.50727.3082 says, "Hello world. The time is: 8/25/2009 14:39:39.8992663". For wsdl, add ?WSDL at the end of the request.

There are similar processes to change and/or test the other services:

Execution Service: http://localhost:8082/qtxs.asmx

or http://localhost:8082/debug

Directory Service: http://localhost:8083/qtds.asmx

32bit ODBC/OLEDB on 64bit Server

This is something that I see questions about all the time. The quick answer is that QlikView 64bit cannot use a driver that is compiled for 32bits.

Why?

If I run an application on a 64bit server in 32bit mode, it has access to all the 32bit OLEDB/ODBC drivers. For example, I have a VBScript that generates OLEDB connection strings. If I run this using the %sysdir%\SysWOW64\csript.exe (the 32bit version of vbscript), I get the following list of OLEDB drivers:



I can see that the Microsoft Jet driver (for Access) is available.

However, when I run my VBScript application in 64bit mode, I get a different list:



No Access driver anymore!

The Microsoft Access drivers are old and have never been compiled for 64bits. This means that a 64bit application running on a Windows 64bit server cannot see them or use them.

QlikView 64bit is not a 32bit application that just runs on a 64bit server, it is an application that is fully compiled to operate in 64bits. I doesn't even know that the Access driver exists because Windows doesn't tell it about it.

So, the limitation is not to do with QlikView, it is a limitation of the company who have delivered the driver.

Luckily, most large database companies (Oracle 10g+, Teradata, Microsoft, etc.) have either OLEDB or ODBC drivers that work in 64bits.

If you just can't get your hands on a 32bit driver? Well, you could go down the route of installing 32bit QlikView client on your server (or another server?) and use that in Command Line mode to access the 32bit driver, read the data and store into QVDs that your 64bit publisher can read. This will of course consume one of your CALs or a developer license, so it is not idea.

The ideal solution is to see what you can do to get a 64bit version of the driver.

Google Maps QVW broken

The Google Maps example that comes with QlikView 9 has stopped working.

This is because Google have made a couple of changes. Luckily, it is resolveable.

Step 1:

Go to http://code.google.com/apis/maps/signup.html and sign up for a Google Maps API key.

This key, once you receive it, should be put into the document variable, gmap_key (the default value of "xx" will no longer work).

Step 2:

The .jpg that we need to put at the end of the URL so that QlikView knows the result is an image will break the URL because it is currently tagged onto the end of the API key. We need to add an additional '&' before the '.jpg' so that Google Maps can interpret the values correctly. It will ignore the "&.jpg" at the end.

- this appears to work OK (in the Colors tab of the Chart properies - Dynamic Image):

='http://maps.google.com/staticmap?center='
&
num(var_mid_lat, '##############', '.', ',' )
&
','
&
num(var_mid_long, '##############', '.', ',' )
&
'&zoom=$(var_zoom)'
&
'&size='&map_size_x&'x'&map_size_y
&
'&key='&gmap_key
&
'&maptype='&var_maptype
& '&.jpg'


Working fine for me now.

Very Useful YearToDate

Many people will have used YearToDate (or Year2Date) to calculate out whether the current date is Year to date for this year, last year, etc.:

YearToDate(date_value)

or

YearToDate(date_value, -1)

But there are some other values of YearToDate that make it very useful. The full syntax is:

YearToDate( date [ , yearoffset [ , firstmonth [ , todaydate] ] ] )

The third parameter is very useful because it allows us to use the one function for MonthToDate and QuarterToDate to compare Year-on-Year or dynamically change between YTD, MTD and QTD (there are functions InMonthToDate and InQuarterToDate that could be used but they would require different Year offset calculations and are less easy to make dynamic use of)

For example:

YearToDate(date_value, 0, Month(Today()))

gives us Month to date this year.

YearToDate(date_value, -1, Month(Today()))

gives us Month to date last year.

With a handy use of the Ceil function, we can also calculate Quarter to date:

Ceil(Month(Today())/3) * 3 - 2

This value will always return the first month of the quarter containing the Month number passed (in this case, Month(Today()) )

So, plugging this into our YearToDate:

YearToDate(date_value, 0, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date.

YearToDate(date_value, -1, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date last year.

Use of appropriate variables means that we can give the user a very useful dynamic swap between YTD, QTD and MTD - if that is required.

"=" sign in the expression builder

There is a very simple explanation for using the "=" sign in the expression builder - it tells QlikView to evaluate everything after the "=" and return the result.

Where there is some confusion is that within charts (and, in successive versions it has been removed in other places) the "=" is not necessary because it is assumed - you wouldn't put an expression in a chart unless you wanted it to be evaluated, would you?

In a text box, the need for the "=" becomes more apparent. If I add a Text Box to my layout and set the text expression to:

Sum(Sales)

Then the Text Box will come to the screen and have a value of "Sum(Sales)". This makes sense because the default interpretation of a Text Box should be that the values in the expression are just text. Now, if I add the "=" into the expression:

=Sum(Sales)

Then the calculation is performed and the result, the sum of all sales based on current selections in the document, is displayed in the Text Box.

It becomes more interesting when we use, or do not use, the "=" in a variable.

For example, if I set up 2 variables, vSales and vTotalSales.

vSales is going to have a value set up in the Expression Builder of:

Sum(Sales)

vTotalSales is going to have a value set up in the Builder of:

=Sum(Sales)

Now, if I set up a straight table and add a dimension, say "Year", and then add 2 expressions - one that is just $(vSales) and the other is $(vTotalSales), then the first column will show sales by Year and the second will show the same value in each row - the total sales for the document.

This happens because the first variable is simply a piece of text - "Sum(Sales)" - that is replaced into the chart expression and evaluated against each dimension. The "=" in the second variable causes it to be evaluated at the document level and it is the result rather than the expression that is inserted into the chart.

The "=" is also interesting in Dollar Expansion. We could achieve the same result as vTotalSales in the chart by using the expression:

$(=Sum(Sales))

This Sum(Sales) is now calculated outside the chart - i.e. at the document level - and the result, not the expression, is returned to the chart.

Knowing about the "=" sign in Expression Builder is very useful to avoid confusion and also to give you a new weapon in your arsenal.

Section Access Gotcha's

Couple of things that can cause you problems in Section Access:

- Loading Null values into the USERID field. You won't get back into your document!

- Loading mixed/lower case values into USERID and PASSWORD. Although they are not case sensitive for users entering them, they must be loaded in CAPITALS.

Sets =- or -=???

This question arose recently: Note: Using gt and lt for Greater Than and Less Than symbols - sorry. What is the difference between the set { < Value = -{"X"} > } and the set { < Value -= {"X"} > }? Well the difference is that the first set is a modification of the $ set and saying that I want all values of the "Value" field excluding the value "X" (it could have also been written as { < Value = {*}-{"X"} > ). The second set is different in that it is additive (or actually subtractive) - it will take whatever selections are on the "Value" field and take off the "X". So, for the first one, if you make any selections on the "Value" field, they will be ignored. For the second one, your selections will be reflected in the result except if "X" is part of your selections it will be excluded. As an example, take the following dataset: Sales: Load * Inline [ User, Sales John, 12222 Tom, 11000 Jane, 19000 May, 21000 Graham, 13300 ]; If I create a straight table with User as the dimension and the following 2 expressions: Sum({ < User = -{"John"} > Sales) Sum({ < User -= {"John"} > Sales) Then whatever selection I make on User will not effect the result - the chart will list the sales for Time, Jane, May and Graham (no John). For the second, if I make selections on a user, the other users will zero out but I will still see no John. Enjoy.

Days360

Recently a client asked me about the Excel function Days360. This is a commonly used function within the financial industry to calculate the number of days between two dates based on a 360 day year - i.e. 12 x 30day months.

The function in Excel takes 2 mandatory parameters and 1 optional parameter:

DAYS360(start_date,end_date,method)

If the "method" parameter is FALSE or left out, then it uses the US (NASD) method. If it is TRUE then it uses the European method. The difference is that in the European method if either the start or end date are on the 31st of the month, they are considered to be on the 30th of the month.

I have a simple VBScript function that replicates this functionality:


Function Days360(StartDate, EndDate, European)

Dim Days1, Days2
Days1 = Day(EndDate)
Days2 = Day(StartDate)

If European and Days1 = 31 Then Days1 = 30
If European and Days2 = 31 Then Days2 = 30

Days360 = DATEDIFF("m", StartDate, EndDate) * 30 + Days1 - Days2

End Function

Unfortunately VBScript doesn't have a MIN function or this could be reduced to be even simpler.

Now, if I define ths VBScript function in my QV Document, I can call it from within the load script in a QlikView application. Since version 8.2 though, I am unable to call macro functions from the chart so I have to have a different solution.

Oleg Troyansky gave a good formula for the month difference: http://community.qlikview.com/forums/p/9923/39894.aspx#39894

MonthDiff = (year(Date1)*12 + Month(Date1)) - (year(Date2)*12 + Month(Date2))

This works great for me. So, I now just need to add the days subtraction. This needs to be two separate functions, one for US and one for Europe.

US:

=(((YEAR(Date2)*12 + MONTH(Date2)) - (YEAR(Date1)*12 + MONTH(Date1))) * 30) + DAY(Date2) - DAY(Date1)

Europe (using the Nummin function to step back a 31st to 30th):

=(((YEAR(Date2)*12 + MONTH(Date2)) - (YEAR(Date1)*12 + MONTH(Date1))) * 30) + Nummin(DAY(Date2),30) - Nummin(DAY(Date1),30)

I hope that this helps others get past this one.


It is worth remembering. No matter what function that you come across that doesn't exist in QlikView, it has to be calcuable some way - and probably is do-able within QlikView.

Does it exist?

In QlikView, we have the option to check if a value is already in a field using the "Exists" clause. I have seen some confusion with it over time, so hopefully this will help.

Exists is an inter-record function that we can use within the load script to check for the existence of a value in a previously loaded field.

The syntax is as follows:

exists(field [, expression])

The field is a field that already exists in our document - which includes any fields in the current load statement.

The expression is a calculation based on the data that we are currently loading.

Note that expression is not a required field. If it is excluded, exists calculates if the value of the field exists in any previous loaded value for that field.

Here is a simple example of using Exists to only load data that exists in a previously loaded field.


Budget:
Load * Inline [
User, Budget
Tom, 10000
Jane, 20000
May, 22000
Graham, 12300
];

Sales:
Load * Inline [
User, Sales
Tom, 11000
Jane, 19000
May, 21000
Graham, 13300
John, 23456
]
Where Exists(User);

The first load statement loads budget information for users Tom, Jane, May and Graham. The second load statement brings in the sales values for users but we state that we only want values for users already exist.

In this case, no values will be loaded for John.

Here, we didn't need to pass an expression (although we could have done Exists(User,User)) because we were looking for a value in the same field that was being loaded.

Here is another example of a similar sales loader:


Sales:
Load
RowNo() As row,
User,
Sales,
If(Exists(User,User), 1, 0) As Exists
;
Load * Inline [
User, Sales
John, 12222
Tom, 11000
Jane, 19000
May, 21000
Graham, 13300
John, 23456
];

If we now put a table of values for row, user and exists, we would see that John in Row 1 has a 0 for exists and in row 6 he has a 1. This shows us that Exists is working on data that is loading in the current load statement also, not just on data that has been loaded before.

The Expression parameter of the Exists statement is very useful - especially if you have complex keys in an Load.