Verify if two factor multiplication are present

[jean_almeida]

"Hello everyone,

how can I verify if both members of a multiplication exist? For example, I'm calculating the gross revenue, which is equal to the price measurement x the volume measurement. However, the price measurement might be missing. I would like to log this as an error if it occurs or create a validation table to check these factors. Is there a way to approach this in SAP Analytics Cloud?"

[hartmut_koerner]

Hi Jean,
there are three challenges for such typical questions:

- What means missing? Distinction between Null and Zero. I assume that you only consider the Null records as errors

- Performance: Avoid IF ... = NULL

- Typically you are not interested in members where neither price nor volume exist

The pattern I use for such cases is the following:
DATA([d/Measures] = "ERRORS") = 2 - (RESULTLOOKUP([d/Measures] = "PRICE") * 0 +1) - (RESULTLOOKUP([d/Measures] = "VOLUME") * 0 +1)

DATA([d/Measures] = "ERRORS") has a 1 when either price or volume is missing and a 0 when both are available.
No record is created when neither price nor volume are available.

Best regards
Hartmut

[N1kh1l]

hartmut.koerner

Will this also work ?

VARIABLEMEMBER #ALL_ENTITY of [d/Entity]

DATA([d/Entity]=#ALL_ENTITY,[d/Account]="Volume")=RESULTLOOKUP([d/Account]="Volume")+(1-1)

IF RESULTLOOKUP([d/Account]="Volume",[d/Entity]=#ALL_ENTITY)>=0 THEN
< some logic>

ENDIF

I want to avoid a null check but all an aggregated level, as my data line should give 0 if volume is null for all entities.

Br.

Nikhil

[hartmut_koerner]

Right. Your formula aggregates the volume of all entities. But I don't understand what the +(1-1) is necessary for.
1 is added and subtracted to the volume of each entity and then the volumes are aggregated.
Best regards
Hartmut

[N1kh1l]

hartmut.koerner

I want to check for a null situation for volume as I have further logic if volume aggregated over entities is NULL ( this will come sku wise)). Currently the code is using =NULL check which I want to avoid. So looking for a alternate way. I look for an alternative to the below

IF RESULTLOOKUP([d/Account]="Volume",[d/Entity]=#ALL_ENTITY)=NULL

[hartmut_koerner]

Hi Nikhil,

this depends on the context. I also have few situations where I cannot avoid the comparison with NULL. But in most situations you have something else that is not null. Let's assume the price. If everything is Null you typically anyway don't need to do anything.
Then you could formulate
DATA([d/Measures] = #1) = (RESULTLOOKUP([d/Measures] = "PRICE") * 0 +1) + (RESULTLOOKUP([d/Measures] = "VOLUME") * 0 +2)
IF RESULTLOOKUP([d/Measures] = #1) = 1
The condition should be fulfilled if a price is available and the volume is Null.

Hope this helps.
Hartmut

[N1kh1l]

hartmut.koerner

Thanks, this really helped. I will have to look for something which always exist and then use this technique.

Br.

Nikhil