How to convert decimals to whole numbers from an auto-calculated column (sql in Table Transformer)

[MudCo]

Hi Team,

I work with a team to keep track of engagements. To produce the required calculated coloumn, I've used table transformer sql that calculates the date difference between the date 'contract will expire' and 'Today's' date (calculation query in internal transformer) and it's colour coded (another external transformer). The issue is, it's showing teh calculated values in decimals instead of whole numbers. It only shows for some rows, not all though. Below yellow there is green as well, and some rows there also show in decimals.

.

Here are my queries:

Internal transformer for date calculation:

SELECT *,
DATEDIFF(day,"today",'Date Contract will Expire')
AS 'Days left from today'
From T1

External transformer for colour code:

SELECT *,
CASE
WHEN ('Days left from today') IS "No dates" OR ('Days left from today') IS "No Date of completion"
THEN FORMATWIKI("{cell:bgColor=#CCCCFF|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') <=-1 AND ('Days left from today') >=-10000
THEN
FORMATWIKI("{cell:bgColor=#FF6666|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') >=0 AND ('Days left from today') <=14
THEN
FORMATWIKI("{cell:bgColor=#FF9966|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') >=15 AND ('Days left from today') <=42
THEN
FORMATWIKI("{cell:bgColor=#FFFF99|align=center}" + 'Days left from today' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#99FF99|align=center}" + 'Days left from today' + "{cell}")
END
AS 'Days left from today'
FROM T*

Both are wrapped in a Table filter macro and here is the internal settings. I am not sure if this is what is causing it. 

[Stiltsoft support · Accepted solution]

Hi @MudCo ,

The date difference is calculated in the first internal Table Transformer macro, so, I guess that you begin to see the strange numbers in its preview.

The SQL query is correct, so, you may check the macro settings tab: the date format for your case should be set as "dd M yy" and the field for decimal places should be left blank.

Also from the last screen I see that you have a bunch of macros wrapped one into another directly - are you sure that you are on Cloud and not Data Center? If you are on Data Center, then check the current version of the app - the up-to-date one is 11.2.0, please update if required.

If the issue persists, refer to our support portal and attach the page storage format of your page (upper right corner of the page -> menu ... -> View storage format).

And also if your question is related to our app, it is better to contact our support directly. In the Community we try to find relevant questions by tags manually and some posts can be easily missed (for example, this question was created under the Jira Software section).

[Stiltsoft support]

And as I see here you've given not the whole structure - that's why we always ask for the storage format.

In the first SQL query you count the date difference and show the column as 'Days left from today'. Here you may get proper numbers or smth strange if the 'Date Contract will Expire' column is empty.

But in the second query your first statement is

WHEN ('Days left from today') IS "No dates" OR ('Days left from today') IS "No Date of completion"

As I see it, somewhere should be an extra step where you put these "No dates" and "No Date of completion" statements in your 'Days left from today' column. 

[Stiltsoft support]

Also got an idea from our developers: maybe you get such numbers because of the winter/summer time (the daylight saving time shift).

So, you may use the ROUND() function and get the even integer number using smth like this:

ROUND(DATEDIFF(day,"today",'Date'), 0)

[MudCo]

Hi @Stiltsoft support  sorry for the late response. 

I am unaware what the storage format means. And I also missed to mention that the colleagues who are finding the report showing in decimals are located in AU and I am located in India. The screenshot I provided was from the colleague from AU region. Everyone here who use this in India region are seeing whole numbers but the ones in AU still get decimals. So I am actually wondering if it's your second comment about the daylight saving that could be the culprit here. 

So if I do use your ROUND(DATEDIFF(day,"today",'Date'), 0), I will use this in my first part of the code as below and see if that works

SELECT *,
ROUND(DATEDIFF(day,"today",'Date Contract will Expire')
AS 'Days left from today'
From T1

[MudCo]

Hi @Stiltsoft support the round function worked. I tried even cast function and it worked well too. Thanks very much for your suggestion.