Table Transformer: Year gets thousands separator, 2,020.00 instead of just 2020

[Torkil Sinkaberg Johnsen]

In the table transformer macro, I have an SQL that looks something like this:

SELECT
YEAR(CAST(Month AS date)) AS 'Year'
, OtherNumber
(...)

I want "OtherNumber" to have thousands separator and two decimals, but I want year to just look like "2020" instead of "2,020.00", without decimals or thousands separator.

I have tried casting to string:

CAST(YEAR(CAST(Month AS date)) AS string) AS 'Year'

But that just gave me "NaN" as values.

I have also tried to set date format to "yy" in the table transformer macro, but that does nothing either.

Any tips?

[Andrey Khaneev _StiltSoft_ · Accepted solution]

Hi @Torkil Sinkaberg Johnsen,

I tried and this query works for me

CAST(YEAR(CAST(Month AS date)) AS string) AS 'Year'

You can try this:

YEAR(CAST(Month AS date))::string AS 'Year'

 or

YEAR(CAST(Month AS date)) + " " AS 'Year'

[Torkil Sinkaberg Johnsen]

No go with any of these. Even tried 

YEAR(CAST(Month AS date)) + "a" AS 'Year'

Ends up with saying "NaNa" ...

Must be an old version I am running. Very frustrating. 

Thanks for your help!

[Andrey Khaneev _StiltSoft_]

Try to update the Table Filter and Charts app then. If this doesn't help, please open a support ticket.