Round Function does not work

[Daniel1908]

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

[rubenmarin]

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?

[Daniel1908]

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

[rubenmarin]

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

[Daniel1908]

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

[rubenmarin]

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

[Daniel1908]

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)) 

[rubenmarin]

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) 

[Daniel1908]

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

[rubenmarin]

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