Need Help with simple addition

Questions & Answers
1

@gerda.grantina

Screenshot 2023-03-20 at 7.06.38 PM
Screenshot 2023-03-20 at 7.06.38 PM1920×728 115 KB

Build rating is nothing but Addition Of of Function Cal + UI Cal + Sev Cal + Build Cal / 4

All of this custom field holds Build Quality parameters and returns Decimal value.

IIf(([Measures].[Complexity] > 0),

CASE
WHEN (([Measures].[Build Count]) = 1) THEN  "5.0"
WHEN ([Measures].[Complexity] > 3) THEN IIf(([Measures].[Build Count])<4,"4.5",IIf(([Measures].[Build Count])<8,"3.5",IIf(([Measures].[Build Count])<12,"2.5","NA")))
WHEN ([Measures].[Complexity] = 3) THEN IIf(([Measures].[Build Count])<=2,"4.5",IIf(([Measures].[Build Count])<=4,"3.5",IIf(([Measures].[Build Count])<=6,"2.5","NA")))

ELSE
  "Nothing"
END
, "NA")

This code is not allowing me to pass value without “” which convert this code into String.
I have already selected Decimal as return value. It is giving me a error while trying in Build Rating Calculated Field.

Please do the needful.

2

Hi @chinatn9624,

The problem with the “Build Rating” calculation stems from the calculated measures used in it. You try to perform mathematical operations with strings, and subsequently, the error.

As a start, I recommend altering each part of the “Build Rating” calculation to return only numeric values – without “NA” and “Nothing”. The calculated measure formula you shared could look similar to the one below:

CASE
WHEN (([Measures].[Build Count]) = 1) THEN 
  5.0
WHEN ([Measures].[Complexity] > 3) THEN
  IIf(
    ([Measures].[Build Count])<4,
    4.5,
    IIf(
      ([Measures].[Build Count])<8,
      3.5,
      IIf(
        ([Measures].[Build Count])<12,
        2.5,
        NULL
      )
    )
  )
WHEN ([Measures].[Complexity] = 3) THEN
  IIf(
    ([Measures].[Build Count])<=2,
    4.5,
    IIf(
      ([Measures].[Build Count])<=4,
      3.5,
      IIf(
        ([Measures].[Build Count])<=6,
        2.5,
        NULL
      )
    )
  )
ELSE 0
END

The numeric values are numeric. “NA” will be replaced with empty, and the calculation will return 0 instead of “Nothing”. You can use conditional cell formatting to highlight these values if required. Update the rest of the calculated measures involved in the calculation of “Build Rating” in a similar fashion.

Best,
Roberts // support@eazybi.com