Nested IF Statement Comparing Versions

[Freddie]

Hi all,

I want to implement the following logic: Write a "1" to Measure = Mismatch_Indicator for every member in "Site" Dimenstion if sales are above zero in Version = Public.Z AND there are no sales in Version = Public.X. 

The idea is to flag this site.

The only difference on these two versions, is that in version Public.Z, Sales are placed on d/DIM1 = "#" and d/DIM2 = "#". Both are placed on a Site.

I have tried with the following script, however I don't get the desired output.

CONFIG.GENERATE_UNBOOKED_DATA = ON

//SCOPE
MEMBERSET [d/Measures] = "Mismatch_Indicator"
MEMBERSET [d/Date] = [d/Version].[p/From_Date] TO [d/Version].[p/To_Date]

//DATA WRITING

IF RESULTLOOKUP([d/Measures] = "Net_Sales_ext", [d/Version] = "public.Z", [d/DIM1] = "#", [d/DIM2] = "#") > 0 THEN
IF RESULTLOOKUP([d/Measures] = "Net_Sales_ext", [d/Version] = "public.X") = 0 THEN
DATA([d/Measures] = "Mismatch_Indicator") = 1
ENDIF
ENDIF

[N1kh1l]

@Freddie 

logic: Write a "1" to Measure = Mismatch_Indicator for every member in "Site" Dimenstion if sales are above zero in Version = Public.Z AND there are no sales in Version = Public.X. 

So If you have 100 in version Z and nothing ( null) or 0 in version X for site members you want to flag these

One approach could be you test the condition like below

Value in ver Z + Value in Ver X = Value in Version Z

100+ null or 0= 100

IF RESULTLOOKUP([d/Measures] = "Net_Sales_ext", [d/Version] = "public.Z", [d/DIM1] = "#", [d/DIM2] = "#") +

RESULTLOOKUP([d/Measures] = "Net_Sales_ext", [d/Version] = "public.X") =  RESULTLOOKUP([d/Measures] = "Net_Sales_ext", [d/Version] = "public.Z", [d/DIM1] = "#", [d/DIM2] = "#")

THEN
DATA([d/Measures] = "Mismatch_Indicator") = 1

You have to be careful about other dimensions which you have not mentioned in the post.

The problem in your approach is IF condition filters the dimension context. So your second if will not even get triggered as the dimension context of first if will not be true for second IF. Either put both IF test in AND condition or use above approach.

Rough coding like below

IF RESULTLOOKUP(ver Z) >0 AND  (RESULTLOOKUP (VerX)=0 OR RESULTLOOKUP(ver X) is NULL)

THEN DATA(indicator)=1

ENDIF

Hope this helps !!

Nikhil