URGENT: Multirow formula error

[satya04]

I am using multirow formula to calculate difference using this logic

IF !Contains([Period], "2013")
THEN
([Capital_expenditures]+[Row-1:Capital_expenditures]+[Row-2:Capital_expenditures]+[Row-3:Capital_expenditures])
ELSE 0
ENDIF

Expectation: First value should be against CQ42014 and this should be a result of ((CQ42014+CQ32014+CQ22014+CQ12014)- (CQ42013+CQ32013+CQ22013+CQ12013)).

My result expected differs from result received through the tool. Kindly guide me on this.

[Yoshiro_Fujimori]

@satya04 ,

I tried with the same input data and the same formula on my Designer, and the result is as below.

There might be something I don't see in your expected valuse. Could you share your workflow and input data?

Period	Capital_expenditures	Result
CQ12013	-126787	0
CQ22013	-129043	0
CQ32013	-143513	0
CQ42013	-150331	0
CQ12014	-134739	-557626
CQ22014	-141234	-569817
CQ32014	-149263	-575567
CQ42014	-171490	-596726
CQ12015	-136787	-598774
CQ22015	-141869	-599409
CQ32015	-143717	-593863
CQ42015	-162633	-585006
CQ12016	-137292	-585511
CQ22016	-132448	-576090
CQ32016	-138953	-571326
CQ42016	-157283	-565976
CQ12017	-134575	-563259

[satya04]

@Yoshiro_Fujimori The solution is correct. I would need it to start from CQ42014. Is it possible to state that? I am unable to include it through the "Contain" function.

[Yoshiro_Fujimori]

Hi @satya04 ,

As the current [Period] field is difficult to use in IF statement, I would convert it to Date type.

Formula to convert [Period] to Date type

PeriodStartDate =

DateTimeParse(

  Right([Period], 4)

  + ToString(

      ToNumber(

        Substring([Period],2,1)) * 3 - 2)

,"%Y%m")

Formula in Multi-Row formula

IF [PeriodStartDate] < "2014-10-01" THEN 0
ELSE [Capital_expenditures]
+ [Row-1:Capital_expenditures]
+ [Row-2:Capital_expenditures]
+ [Row-3:Capital_expenditures]
ENDIF

Workflow

Output

I hope this may work for your case.