How to compare two key figure values and copy in Data Action

[Vasu_Sac]

Hello,

Users will enter the data at input page for two key figures Z_SALES1, Z_SALES2 along with other dimensions selections. The key figure Z_SALES1 value always higher than Z_SALES2 key figure for that dimension member combination, but sometimes user will enter Z_SALES1 lower values.

So, I am looking for the solution if users enter Z_SALES1 lower value compared to Z_SALES2, I would like to copy from Z_SALES2 values to Z_SALES1.

I tried the below code at Data Action but getting error –  “ No viable alternative at input ‘DATA’

IF DATA([d/Measures] = "Z_SALES1", [d/ZCCODE] = %CCODE_Input%) < DATA([d/Measures] = "Z_SALES2", [d/ZCCODE] = %CCODE_Input%) THEN

DATA([d/Measures] = "Z_SALES1", [d/ZCCODE] = %CCODE_Input%) =

RESULTLOOKUP([d/Version] = "public.Budget", [d/ZCCODE] = %CCODE_Input%,  [d/Measures] = "Z_SALES2")

ENDIF

How to fix the data action. How to achieve the required solution? Is the above problem statement, is it any other way to update except data action?

Regards,

Vasu

[MoonJun · Accepted solution]

Hi @Vasu_Sac 

The DATA() function can't be in an IF condition on the Advanced Formulas script. To avoid errors, please refer to the script below. 

IF RESULTLOOKUP([d/Measures] = "Z_SALES1", [d/ZCCODE] = %CCODE_Input%) < RESULTLOOKUP([d/Measures] = "Z_SALES2", [d/ZCCODE] = %CCODE_Input%) THEN
	DATA([d/Measures] = "Z_SALES1", [d/ZCCODE] = %CCODE_Input%) =
	RESULTLOOKUP([d/Version] = "public.Budget", [d/ZCCODE] = %CCODE_Input%, [d/Measures] = "Z_SALES2")
ENDIF

I hope this is helpful to you, and if I have misunderstood anything, please feel free to reach out.

Moonjun

[Vasu_Sac · Accepted solution]

Thank you @MoonJun. Its working as expected.