topic Re: Conditional Expression Challenge in App Development

Conditional Expression Challenge

Meret — Mon, 20 Dec 2021 21:38:40 GMT

Hi,

I am trying to create an expression that calculates a measure depending on criteria defined by the target. The goal is a table that shows all the targets with its measures to check if there are any violations.

The data model consists of two tables connected with an ID and the date as the key. Both tables use the same dimensions and codes, but those fields cannot be connected (because of a consequential multiplication of the data).

Here a simplified example of the problem:

The following formula works fine, if an ID is selected:

sum(if($(='['&$(vField)&']')= CODE, MEASURE))

vField = FIELD

However, the goal is to show all the IDs in the same table without a selection (therefore, set analysis is not an option).

How does the formula need to be changed to acchieve this?

Thank you very much for your help.

Re: Conditional Expression Challenge

Hatus — Wed, 22 May 2019 11:41:39 GMT

Very interesting! Hope to see the suggestions.

Re: Conditional Expression Challenge

tresB — Wed, 22 May 2019 11:43:46 GMT

How do you define the variable? Could you share your sample app?

Re: Conditional Expression Challenge

Meret — Wed, 22 May 2019 11:50:07 GMT

Hi Tresesco

Thanks for the fast response.

The variable is defined as:

vField = FIELD

You can find the app in the attachment.

Re: Conditional Expression Challenge

tresB — Wed, 22 May 2019 12:17:13 GMT

Ideally I would resolve this in the data model. However, check if the below expression helps:

sum(if(DIM1= CODE or DIM2= CODE, MEASURE))

Re: Conditional Expression Challenge

Meret — Wed, 22 May 2019 13:04:35 GMT

Thanks for your solution.

Sorry, the example was not clear enough. There is an updated app in the attachment.

In the real app this approach does not work, because the values in the field CODE are not unique. For example, '100' is also used in other dimensions (see DIM3 in the updated app), where it stands for another criteria.

Also, there are nine different dimensions in the real application and in the future, there may be more, so the field must be kept variable.