BAQ calculated fields (ceiling)

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?


I figured out it didn’t like the integer and decimal mix. I added

convert(decimal, in front of where I used the ceiling function and now everything works fine.

1 Like