Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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
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
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
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))
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)
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
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