When I have a calculated field as below, I get numbers that I would expect. Average cost comes from a sub query and is in dollars and cents. @Markup is a paramater to put in the % marked up for sale.
(MtlList.Calculated_AverageCost * (1+@Markup/100))
The sales guys using the dashboard would like everything rounded up to the next dollar so I put in this function and everything is rounded up the next dollar. Easy.
ceiling(MtlList.Calculated_AverageCost * (1+@Markup/100))
Now we want to keep everything that is below a dollar with the cents, and everything above rounded up. I’m thinking that I should be able to run a condition like this to do that.
(case when (MtlList.Calculated_AverageCost * (1+@Markup/100)) < 1.00 then (MtlList.Calculated_AverageCost * (1+@Markup/100)) else ceiling(MtlList.Calculated_AverageCost * (1+@Markup/100)) end)
But when I do, now I get weird results. Everything rounded one way or another, some things are 2.00 instead of the 1.00 that they should be and other things are 0.00. Does anyone know what I am doing wrong to make this work? Do I just have to split these out into individual calculated fields?