cancel
Showing results for 
Search instead for 
Did you mean: 

Verify if two factor multiplication are present

jean_almeida
Explorer
0 Kudos

"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?"

Accepted Solutions (0)

Answers (2)

Answers (2)

hartmut_koerner
Product and Topic Expert
Product and Topic Expert

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
Active Contributor
0 Kudos

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
Active Contributor
0 Kudos

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
Active Contributor
0 Kudos

hartmut.koerner

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

Br.

Nikhil

N1kh1l
Active Contributor
0 Kudos

jean.almeida

For the calculation you dont need to. It will work only if both of A* B are present. But to flag missing A or B from business perspective, you could flag them.

jean.almeidaYou could just check on price being null and flag it with a flag value let say 1 (in a seperate audittrail or same). Present this flag data as a story to business to rectify it. We did a similar approach for our missing rates, we wrote a flag of -1 to rates and created a report with all -1 shown as red using thresholds.

Br.

Nikhil