URGENT: Multirow formula error

Question

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 · Answer

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.

MultiRow.yxmd

satya04 · Answer

@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 · Answer

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

PeriodCapital_expendituresResultCQ12013-1267870CQ22013-1290430CQ32013-1435130CQ42013-1503310CQ12014-134739-557626CQ22014-141234-569817CQ32014-149263-575567CQ42014-171490-596726CQ12015-136787-598774CQ22015-141869-599409CQ32015-143717-593863CQ42015-162633-585006CQ12016-137292-585511CQ22016-132448-576090CQ32016-138953-571326CQ42016-157283-565976CQ12017-134575-563259

MultiRow.yxmd