CASE select not functioning as expected

I have a calculated field which calculates the time in days spent between 2 stages (our cycle time).

I am demonstrating this for the current month and the last month with an additional markdown field demonstrating the change.

As part of this I am using the below selection… but I am not getting the expected equals result where the numbers for both calculated fields are the same.

Any ideas?

 CASE
        WHEN
        -- if cycle time is decreased, show green arrow down
             [Measures].[CYCLE_TIME (This Month)] < 
             [Measures].[CYCLE_TIME (Last Month)]
         THEN
      "<i class='far fa-arrow-down' style='color:green'></i>"
         WHEN
        -- if the cycle time is increased, show red arrow up  
             [Measures].[CYCLE_TIME (This Month)] > 
             [Measures].[CYCLE_TIME (Last Month)]
         THEN
     "<i class='far fa-arrow-up' style='color:red'></i>"
         ELSE
     "<i class='far fa-equals' style='color:orange'></i>"
         END

image

The calculated fields are being converted to Integer. Is there likely to be an issue here, is the comparison being done on a decimal field and therefore being seen as not equal?

Hi @Keir

You are correct about integers and decimals: in the calculation, the decimal value is compared, regardless of the formatting.

You may compare only rounded values, use Round() function for that: Round

Round([Measures].[CYCLE_TIME (This Month)], 0) <
Round([Measures].[CYCLE_TIME (Last Month)], 0)

If you want to display some character also for cases when the values are equal, then add a specific WHEN condition for a case when the values are the same (with = instead of < or >).
Otherwise, for equal (rounded) values the field will be empty (it is ok, if this is your case).

Best,
Ilze / support@eazybi.com

1 Like

Thanks @ilze.leite - will give that a go :smiley: