Skip to Content
0
Jun 08, 2022 at 06:25 AM

IF between periods - when periods are set in accounts

147 Views

Hi

I'm working on an investment project (granularity: year)

I have created accounts to represent years that have to be filled per project for example:

image.png

the thing if that I have to create a data action with a condition that would say:

If the year is between current year and launch year do something (let's say delete a spesific account)

then my expected result would be:

between-periods.png

but I'm couldn't to do it because I want those years to be dynamic and not hard coded

and I didn't find the way to do it with "IF" formula or with Memeberset

these are some of my trials can you tell me were I'm wrong?

(bellow print-screens and code for your convenience)

between-periods1.png

between-periods2.png

IF [d/Date] >= INT(RESULTLOOKUP([d/BC_ACCOUNT] = "PRM04_Y_PY"))
	THEN
	IF
		[d/Date] <= RESULTLOOKUP([d/BC_ACCOUNT] = "PRM05_Y_LNCV")
		THEN
		DELETE([d/Measures] = "VALUE", [d/BC_ACCOUNT] = "KPI15_POST_LNCH_OPTX")
	ENDIF
ENDIF
VARIABLEMEMBER #CURRENT OF [d/Date]
VARIABLEMEMBER #LAUNCH OF [d/Date]

DATA([d/Date] = #CURRENT) =
RESULTLOOKUP([d/Date] = %P_0_YEAR%)
DATA([d/Date] = #LAUNCH) =
RESULTLOOKUP([d/BC_ACCOUNT] = "PRM05_Y_LNCV", [d/Date] = "2020")



IF [d/Date]>=RESULTLOOKUP([d/Date]=#CURRENT)
THEN
IF [d/Date]<=RESULTLOOKUP([d/Date]=#LAUNCH)
	THEN
		DELETE([d/Measures] = "VALUE", [d/BC_ACCOUNT] = "KPI15_POST_LNCH_OPTX")
	ENDIF
ENDIF

thanks

Attachments

image.png (12.4 kB)