past 12 months for every month

[Channa]

Hi All i have data like attach file

from 2017 to 2021 April

for 2021 April i need sum of (2021 April to 2020 March)

Date: first date of every month

can you please help me with this

[Luke_C]

Hi @Channa 

Here's one method with the multi-row formula tool. Essentially for a given record it will add the sales for the last 12 rows. Perhaps this can help get you started. 

Curious if others have alternatives to this, it doesn't strike me as the most elegant solution but should get the job done. 

[fmvizcaino · Accepted solution]

Hi @Channa ,

Here is my suggestion for you.

I was doing the same as @Luke_C , but since he got here first and asked for different solutions, I've created another one, but not elegant as well, unfortunately.

If you need to sum the full amount, you only need to replace the join tool for a find/replace tool.

BEst,

Fernando Vizcaino

[apathetichell]

Append the starting field to a new column and you can use this iterative macro...

I used summarize/min date and then append fields to create the new column...

[Channa]

Thank you @Luke_C

can we add Country and Multiple products for this same Country and Month

this is code works fine if i have one row for one month in my case i have multiple products

can you please help me

[Channa]

Hi @fmvizcaino

Luka_C Solution will not work in my case because i have multiple rows for each month

i need add one more column as MaxMonth for last 12 months and total value  also need to sum for 12 months by prod and county

[Channa]

i need something like

Month       Country     Prod    Sales

202104  INdia            Prod1    10000(it is sum of last 12 months for same prod and country)

[apathetichell]

I re-worked some of my macro to break these out- and with 60 product types and incomplete data it's all jumbled. There are some macro errors still so there are no entries for 12 month gaps, and apparently duplicate entries where there are no changes.

I've attached how it looks now and count shows you how few values are going into each entry.

It also may look better  and make more sense with your full dataset since again as-is with this dataset it needs work.  I won't have time to fix it right now - but It might work better sorted in descending order with an end date and a macro working in reverse order with a descending date datetimediff([endingdate],-1,"days") although you obviously don't have the cushion of datetimenow() as the end date.

[fmvizcaino]

Hi @Channa ,

Here it is.

Best,

Fernando Vizcaino

[Qiu]

@Channa 
I tried something dynamic.