Time & Date Functions not working in Web-Player Stored Data Report

[Michiel van Schijndel]

I have a calculated column in my report that is making use of a date function. Basically I have records in my table that have a registration date, and I'm using a calculated column that compares today's date with the registration data to indicate if something happened in the past or is actually planned for the future.

 

The table & report seem to work fine in the Spotfire Client. However when I release the report as a report in the Spotfire library, as stored data, all the functions/columns relating above mentioned time/date formula become empty. I'm using the DateTimeNow() formula as a basis for the report, and I'm guessing that's causing the issue. I'm however not sure whether this issue is being caused by a stored data report not being able use "DateTimeNow()" in the report vs a live-connection report, or whether this is an issue of the Spotfire Client vs Spotfire WebPlayer

Does anyone know if that's the case, and/or if the problem would be solved if I would release the report as linked data Or is this something that's generally a problem in the WebPlayer vs the Client

 

Thanks in advance,

[John Justus 5]

what happens when you add the Registration date and the datetimenow() to the table. Does that show on the webplayer when you store / embed the data

 

You might need to some debugging. It should display the calculated values even though the data is stored / embedded. Please let me know how it goes.

 

Thanks,

 

John

[Michiel van Schijndel]

The problem is that there is no error/bug message so there is also nothing to debug. I did some further investigation, and the problem seems to be in this exact calculated column.

 

I have 1 Period column that is coming from the data itself in the format "2020-001", "2020-002", "2020-003", etc.

I use that column to define the last day of each period, as per below:

 

Column Name: Last day of period (screenshots of the data table are in main post)

Date(If(right([Period],2)="01",Concatenate("31-01-",left([Period],4)),If(right([Period],2)="02",Concatenate("28-02-",left([Period],4)),If(right([Period],2)="03",Concatenate("31-03-",left([Period],4)),If(right([Period],2)="04",Concatenate("30-04-",left([Period],4)),If(right([Period],2)="05",Concatenate("31-05-",left([Period],4)),If(right([Period],2)="06",Concatenate("30-06-",left([Period],4)),If(right([Period],2)="07",Concatenate("31-07-",left([Period],4)),If(right([Period],2)="08",Concatenate("31-08-",left([Period],4)),If(right([Period],2)="09",Concatenate("30-09-",left([Period],4)),If(right([Period],2)="10",Concatenate("31-10-",left([Period],4)),If(right([Period],2)="11",Concatenate("30-11-",left([Period],4)),If(right([Period],2)="12",Concatenate("31-12-",left([Period],4)))))))))))))))

 

In short: The formula looks at the end of the Period field (e.g. 2020-001) and then defines which month that is, and then basically concatenates "Last day of month + month + year" to form a date and then transforms the property of that field also directly into a date format.

 

This formula is working fine as a calculated column in the Spotfire Client. However as soon as I release it to the Webplayer as a stored data report, this column returns empty.

 

It seems so weird to me because I have tried to add a calculated column simply as DateTimeNow() in the report just to test if that works and that's not giving a problem at all. Above mentioned formula works in the Client, and all I do is release the report to the Webplayer (as stored data) and it suddenly stops working. I don't have a bug/error message indiciating what's wrong, it just shows the column but without any values as in attached screenshot.

[Michiel van Schijndel]

The weird thing is that if I remove the Date() part of the formula (so that I make the concatenate as a text string) then it is working fine. Somehow the Date() part seems to cause a problem when I release the report as stored data.

I would continue as a text string, however since I'm using the outcome of that formula as a reference to compare that date between the current date, I have to have the field as a Date Field since I can't calculate based on a text string field...

 

Also I found that if I go to the calculated column menu and keep the formula as a date formula but press "Freeze Column" on the settings, it seems to work. I have no idea however why that is, and if I'm hampering any data/reload options if I keep this function active. Does anyone know

[Michiel van Schijndel]

Anyone

I narrowed it down to this exact formula that is somehow causing a problem in WebPlayer vs Spotfire Client:

 

I have a column that is called "Last day of period", it's a calculated column thatbasically takes a month value from another column and calculates the last day of that month and returns it as a text string. So example output would be 31-01-2020, 29-02-2020, etc.

In order to do calculations with above formula I have another calculated column that simply makes a data of the column, so the formula is:

Date([Last day of Period])

 

Literally all it does it change the string format of that column to a date column.

 

That column, works fine in the Spotfire Client, with linked data. As soon as I release the report however as stored data in the WebPlayer, the column just returns empty..... So any other column that refers to that column also returns empty. I get no error or debug message whatsoever.