Show only one of column values in higher granularity level

Question & Answer
,
1

I have a field named Id, a field named accounting code and one named budget check.

Budget check may be on budget, out of budget, pending budget check or undefined on accounting code level.

Now I want to create a calculated field to show a report on id level with the below rule.

If at least one accounting code is out of budget the whole id is out of budget.
If at least one accounting code is undefined the whole id is undefined.
So for example in the below picture 61269 has an accounting code On Budget and one Out of Budget. The whole ID should be out of budget.
The same for 58552 which should be Undefined etc.

image
image297×425 17.6 KB

2

I managed to do it with the below calculated field.

ifelse(
    max(ifelse({budget_check} = "Out of Budget", 1, 0),[{form_id}]) = 1, "Out of Budget",
    ifelse(
        max(ifelse({budget_check} = "Undefined", 1, 0),[{form_id}]) = 1, "Undefined",
        ifelse(
            max(ifelse({budget_check} = "Pending Budget Check", 1, 0),[{form_id}]) = 1, "Pending Budget Check",
            ifelse(max(ifelse(isNull({budget_check}), 1, 0),[{form_id}]) = 1, NULL,
            "On budget"
            )
        )
    )
)