Web issue with functions and zero values

Post Reply
Regular Participant
Posts: 171
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Web issue with functions and zero values

Post by Mark RMBC » Fri Mar 09, 2018 12:35 pm


I am experiencing a few issues when trying to create a journal template on TM1Web, particularly around validating the data.

This template will allow users to input into many rows and allows for multiple measures to be entered and multiple values. So validation of the values is required.

This template is not an active form in any way, it is simply an excel document which does some DBSW into an holding cube if the validations are valid.

First issue (trivial):

I have been trying to use the excel Search function in a websheet and noticed that while the formula worked in perspectives it didn't work on the web.

After some trial and error testing I got it to work by specifying the start number parameter in the search function.

This doesn't seem to have been documented? Is this a known requirement that I have somehow missed along the way?

Second issue (a real annoyance):

I have a column for Adjustment Value which is formatted to String. I want to validate certain values entered into this column.

I have the following formula to test the validation:

=IF(G6="","", IF(OR(AA6=1,AB6=1,AC6=1),1,0))

G6 is the adjusted value column

This works perfectly ok in perspectives.
On the web this formula works fine when the value entered into Adjusted Value column is not zero, but as soon as I enter zero the formula doesn’t work, even when AA6 is 1 or AB6 is 1 or AC6 is 1. It appears to treat zero as blank on the web, but the result of the above formula is 0, not blank!

If I add into a formula Iserror(Search(“0”,G6) then this can get the formula to work and recognise the vale as zero and not blank.

Any words of wisdom would be most welcome!

Cheers, Mark

Post Reply