Add some based on accumulative data

[cl1818]

What tool is best for handling monthly cumulative data with irregular updates? What tool should be used when working with monthly cumulative data, where the values vary—some months may have no additions, while others may have significant changes? I need to just add all the values per month whether it's 3 months or 36 months.

[KGT]

You need to create a field for Month and then summarise by that field. You can make a month field off your Date field with DateTimeTrim([Date],'month')

[Yoshiro_Fujimori]

Hi @cl1818 ,

In general, if you want to deal with time-series data effectively, you may consider to "Transpose" it to put all the time-series values in one column.

Then you can compare the deta between consecutive rows with "Multi-Rows Formula".

I am not exactly sure what you want from the sample output, but here is a sample workflow assuming you want to add all the changes by each group.

Workflow

Formula Tool

[Month] = DateTimeParse([Name],"%b-%Y")

[Amount] = ToNumber(Replace([Value], "£", ""))

Multi-Row Formula Tool

[Delta] = [Amount] - [Row-1:Amount]

* If you want to capture all the "changes" regardless of the direction (increase or decrease), you may apply abs() function to this value.

Summarize Tool

[ADJUSTMENT_AMT] = Sum_Delta

[Month_From] = Min_Month

[Month_To] = Max_Month

Group by: [MemberID], [Scheme], [Code], [ID], [Ref]

Output

I hope this helps. Good luck.

[Yoshiro_Fujimori]

It may be better to ignore the first line of each group (as it is a start point, not a "change".)

If that is the case, you may modify the expression in "Multi-Row Formula" Tool as below.

IF IsEmpty([Row-1:Scheme])
THEN 0
ELSE [Amount] - [Row-1:Amount]
ENDIF