topic Re: Round Function does not work in App Development

Round Function does not work

Daniel1908 — Thu, 29 Apr 2021 12:01:33 GMT

Hello together,

I am using the below formula, unfortunately the round function does not work.

=Count({<Price={0},[ReturnLabel.]={"R*"},[Key]-={2578}>}
If(round(Price,0.01)<round([Price1],0.01),Material,null()))

Price is loaded in the script as below:

if(floor("Price1")-(floor("Price3"+"Price4"))=0,'1','0') as Price

Thanks for your help.
Kind Regards
Daniel

Re: Round Function does not work

rubenmarin — Thu, 29 Apr 2021 13:33:06 GMT

Hi, why you say it doesn't works? can you post an example? If Price is an integer value rounding it wo 0.01 will return 0 or 1, nothing to round here, maybe you expect something differnt for the Round function?

Re: Round Function does not work

Daniel1908 — Thu, 29 Apr 2021 15:13:13 GMT

Hi,

the difference between Price1 and (Price3+Price4) is 0,01€.
The Material should not be counted in the table.

Price1 is 190,99€
Price3 is 95,5€
Price4 is 95,5€

=Count({<Price={0},[ReturnLabel.]={"R*"},[Key]-={2578}>}
If(round(Price,0.01)<round([Price1],0.01),Material,null()))

Price is loaded in the script as below:

if(floor("Price1")-(floor("Price3"+"Price4"))=0,'1','0') as Price

Kind Regards,
Daniel

Re: Round Function does not work

rubenmarin — Sat, 01 May 2021 07:17:12 GMT

Hi Daniel, by the way Price is loaded it only can have 2 values: 0 or 1, thats what if(floor("Price1")-(floor("Price3"+"Price4"))=0,'1','0') as Price is doing

Using Price={0} you use set analysis to only keep records where Price is '0'. And the 'If()', picking the example values it would be:

If(0<190.99, Material, Null()) -> If() is true so it returns Material (at least if it has ReturnLaber="R*" and Key<>2578)

The other possible value for Price is 1 so the If() would be:

If(1<190.99, Material, Null()) -> If() is true so it returs Material (at least if it has ReturnLaber="R*" and Key<>2578)

Both are comparison that doesn't really maks sense so I think that at some point you are doing something different to your intentions

Re: Round Function does not work

Daniel1908 — Mon, 03 May 2021 11:20:54 GMT

Do you have a solution how to exclude the ones which have a difference <1€?

I tried to load the below in the script editor, unfortunately it did not work.
My intension was to exclude every value <1€ from PriceDifference.

SUM("Price1")-("Price3"+"Price4") as PreisDifference

Kind Regards,
Daniel

Re: Round Function does not work

rubenmarin — Mon, 03 May 2021 15:49:15 GMT

Hi, this should be something like:

Count({<PreisDifference={">-1<1"}>} Material)

And PreisDifference can be calculated as:

SUM("Price1"-("Price3"+"Price4"))

or: Rangesum(Sum(Price1),-Sum(Price3),-Sum(Price4)) // in case some of the fields can be null

Re: Round Function does not work

Daniel1908 — Mon, 03 May 2021 16:09:28 GMT

With both I get the message "Invalid expression"

Any Ideas what could be the reason?

SUM("Price1"-("Price3"+"Price4"))

Rangesum(Sum(Price1),-Sum(Price3),-Sum(Price4))

Re: Round Function does not work

rubenmarin — Tue, 04 May 2021 06:38:47 GMT

Are you using a group by?, on your last post you used a Sum(), for that you need to have a group by clause, if not you can omit the Sum:

-"Price1"-("Price3"+"Price4") //or "Price1"-"Price3"-"Price4"

-Rangesum(Price1,-Price3,-Price4)

Re: Round Function does not work

Daniel1908 — Wed, 05 May 2021 09:57:48 GMT

Hi,

"Price1"-("Price3"+"Price4") as Price Comparison worked fine.

Do you know how to show only 2 decimal places for Price Comparison?

Kind Regards
Daniel

Re: Round Function does not work

rubenmarin — Wed, 05 May 2021 11:35:03 GMT

Hi, round() will round that value to the most nearest value: Round("Price1"-("Price3"+"Price4"), 0.01)

Num() can change the format, internally stores the full value but only shows 2 decimals: Num("Price1"-("Price3"+"Price4"), '#.##0,00') // Or #,##0.00 using dot as decimal separator

Re: Round Function does not work

Daniel1908 — Wed, 05 May 2021 11:43:28 GMT

That worked, thank you very much for your help.

Kind Regards,
Daniel

Re: Round Function does not work

Daniel1908 — Mon, 10 May 2021 08:38:18 GMT

Hi,

I added Num("Price",'#.##0,00'), to the script, but the price is still shown as below.

How do I get 1.998,25€?

Kind Regards,
Daniel

Re: Round Function does not work

rubenmarin — Mon, 10 May 2021 14:57:47 GMT

Maybe column format is overwriting field format. Or just apply he same to the column or in the object itself.

What is that? a KPI, a table cell?

Re: Round Function does not work

Daniel1908 — Wed, 12 May 2021 08:58:24 GMT

I added Num("Price",'#.##0,00') into the column.

This works fine.

Thanks for your help.

Kind Regards,
Daniel