Direct Query : Issue with decimal values

[Antoine04]

Hello guys,

I am having an issue while using Direct Query and my DATA stored in Snowflake.

The case is as simple as this :

I have a field "TOTAL" and I want to calcule : Sum(TOTAL).

It does not work because it seems DIrect Query does not like decimal values.

When I am calculating Sum(TOTAL_ARRONDI) where TOTAL_ARRONDI is TOTAL rounded, It works.

Any idea about how to solve this ?

Thanks !

[marcus_sommer]

You may try to play with the number-format and replacing the comma with a dot.

[Antoine04]

I tried already, but it did not work neither.

I just think it's not possible to sum decimal values with direct query. Quite annoying 😅

[marcus_sommer · Accepted solution]

Never worked with direct query but I doubt that there is a general issue with decimal values. I think it's rather related to the applied settings and/or the way the data are processed. For example if your default number-format has a comma as decimal delimiter and you assigned a timestamp to variable like:

let v = num(peek('timestamp', -1, 'table'));

your variable will contain a valid value with a comma. Just displaying v in a text-box or similar will work without an error but if you want to calculate with it like:

let x = $(v) + 1;

it will fail. A similar behaviour may occur by various points within the processing.

Beside this you may look for workarounds like multiplying the values at first with something like 1000 and making an integer from the float without loosing information and later it's divided again. Also thinkable would be approaches like fetching the values with floor() and frac() and their lengths and also matching them lately.

[Antoine04]

Thank you for your help Marcus !

Regards