Handling formula errors in Web Intelligence

Don’t you just hate it when you see a report with DIV/0 errors?

Thankfully, Web Intelligence provides us with a function to handle these types of errors, allowing us to provide a more meaningful message.

Lets look at IsError

IsError() is a function which will return a boolean value.

A 1 is returned is the formula being evaluated is in error. A 0 if there is no error.

This function is particularly useful when embedded into an IF statement:

Lets assume x = 6 and y = 0

IF (IsError(x/y);”The y object is zero – a DIV/0 error has occured”;(x/y))

In this example, the statement x/y is being evaluated within the IsError function:

6/0

As you can’t divide by zero, a DIV/0 error has occured and a 1 is returned by the IsError statement.

The IF statement then presents one of two outcomes depending on this boolean value.

In the case of an error, the user is presented with some meaningful text:

“The y object is zero – a DIV/0 error has occured”

If we change the y value to 3, the IsError function returns 0 – no error, and the second part of the IF statement is evaluated: x/y – which gives us a value of 2.

It is good practice to include error handling into your reports, particularly when you are performing any type of division task. Wrapping an If IsError check around your formula ensures that these type of errors are handled in the correct manner and should help the user identify how the problem can be fixed.

Advertisements

How to calculate the number of days in a month in WebIntelligence

A quick method of determining the number of days in a month in WebIntelligence is through the use of the LastDayOfMonth function.

For a specified date, the LastDayOfMonth will give you the last day in that month.

=LastDayOfMonth(CurrentDate())

To extract the number of days, we want to use the first two digits of this result. The following formula will acheive that:

=Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2)

We’ve added two functions here. The first is FormatDate. LastDayOfMonth returns a value held as a date field. We ultimately want the first two digits of this field. However, using the Left() function means we have to have a string input, not a date input. We have to convert the input into a string format first.

This requires two steps.

The first is to convert the LastDayOfMonth value into a string. We do this using the FormatDate function:

=FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”)

We now have a string.

We then extract the first two digits to give us the number of days in a month:

=Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2)

If we want to subsequently use this in a calculation, we need to further manipulate this value and convert it to a number. This is achieved using the ToNumber function:

number ToNumber(string number_string)

This takes a string value and converts it to a number.

In our case, we create the following formula:

=ToNumber(Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2))

We now have the number of days for a particular month that we can use in our calculations.

Missing FirstDayOfMonth() function…

Sadly, there isn’t a function to determine the first day of the month. However, we have a method of extracting the last day of the month using LastDayOfMonth() and using the formulas described above, we can work out the first day:

=RelativeDate(LastDayOfMonth(CurrentDate());-ToNumber(Left(FormatDate(LastDayOfMonth(CurrentDate()) ;”dd/MM/yyyy”);2))+1)

Despite looking complex, we are subtracting the number of days in the month (plus one) from the last day of the month. If we subtracted the total number of days in the month, we would end up with the last day of the previous month! We don’t want this. So we add a 1 to give us the first day.

Also note that in order to subtract days, we have prefixed the ToNumber function with a minus symbol.

Update:

Hat tip to JB for pointing out that we can avoid the string conversion by using the DayNumberOfMonth function. 

Hence, to calculate the last day of the month:

=DayNumberOfMonth(LastDayOfMonth(CurrentDate()))

And the first day of the month:

=DayNumberOfMonth(RelativeDate(CurrentDate(); 1-DayNumberOfMonth(CurrentDate())))

How to convert a string prompt into a date in WebIntelligence

 There are occasions where we need to prompt the user for a date. This can be done using the prompt functionality in the query panel.

We can display the value entered by the user at the prompt using the UserResponse function.

The UserResponse function is made up of two parts:

string UserResponse(string data_provider;string prompt)

The first part is the Data Provider that contains the prompt that you want to use. This is an optional field and is only required if your report contains multiple data providers. If you are only using one data provider, ignore this first part.

The second part is the EXACT text of your prompt string.

This needs to be a perfect match to your prompt. A way to ensure this is to copy and past the prompt string before you refresh the report.

When refreshed, the function will return the prompt value.

Note that the value returned from the UserResponse function is a string. Despite the value being shown as a date, the value is stored as a string. This means we are not able to perform date calculations on this field as it stands. The error message that will be shown is:

 

To overcome this, we expand the formula slightly and nest the UserResponse function inside a ToDate function.

The ToDate function is made up of two parts:

date ToDate(string input_string;string date_format)

The first part is the input string (in our case the UserResponse formula), and the second part is the format of the input. Note that the format has to match the input exactly.

We end up with the following formula:

 =ToDate(UserResponse(“Enter Date:”);”dd/mm/yyyy”)

At this stage, the user prompted date is converted to a date format and can be used in date calculations.

Bonus:

If you subsequently want to display the user prompted date in a specific date format, we would nest the formula further and include a FormatDate function:

string FormatDate(date date_to_format;string date_format)

This function takes two values. The first is the date you want to modify and the second part is the format string you want the date to appear in.

For example, if the user has entered the value 14/01/2012 at the prompt and we want to display this as Saturday 14 January 2012, we would create the following formula:

=FormatDate(ToDate(“Enter Date:”);”dd/mm/yyyy”);”Dddd dd Mmmm yyyy”)

In the above example, note that the output of the FormatDate function is a string, not a date. This is important to remember. If you need to perform calculations on a date, make sure it is in a date format (using ToDate).

Once you have finished your date calculations, you can then format the output in any way you like using FormatDate.

For a list of available date formats, take a look at this post. https://bobjblog.wordpress.com/2012/03/05/web-intelligence-date-formats

Note: If you receive #ERROR messages in your calculations, ensure that you are not using a date and time string. The examples above are based on dates in the format mm/dd/yyyy and not mm/dd/yyyy hh:mm:ss

Web Intelligence Date Formats

A simple post to highlight the different date formats that are available in Web Intelligence.

%d bloggers like this: