Creating Quarterly Totals from Monthly Data

[PiersM]

Hi - 

I'm trying to create quarterly totals from a monthly series of data - Field 'Total (t)'. I've tried using the multi-row formula tool but if I Group by month I simply replicate my existing monthly number when entering the expression [Row-2:Total (t)] + [Row-1:Total (t)] + [Total (t)], while if I group by quarter then I remove every third month from the chronology and put it to the end.

What I am doing wrong?

Thanks

Piers

[fmvizcaino]

Hi @PiersM ,

I've inserted some tools before your multi-row tool in your workflow. Please take a look

Best,

Fernando Vizcaino

[AbhilashR]

Hi @PiersM, you could use a Summarize tool in your workflow and set it up to GroupBy on Quarter and sum on Total (t) column to add the values by quarter. 

EDIT - just saw @fmvizcaino's solution after I posted my note. The approach he has is what I too was suggesting. 

[PiersM]

Thanks Fernando - 

I could be wrong but in the Summarize tool you inserted I can now see the total for Q1 2018 as 10,732.624. This is the value for March 2018 but what I am looking for is the sum of Jan, Feb and March - so the sum of 2018-01-01 at 10,717,554, 2018-02-01 at 11,151.779 and 2018-03-01 at 10,732.624 = total of 32,601.957. And similarly the sum of April/May/Jun, Jul/Aug/Sep and Oct/Nov/Dec for both 2018 and 2019. What I am seeing in both the Summarize and Join tools that you inserted is that the rows with values for each Quarter have been taken out of chronological sequence.

Best,

Piers

[fmvizcaino]

Hi @PiersM ,

Would you be able to share a sample dataset of both files from your workflow? That way I can check the numbers you are saying and also to check the structure built for your quarter column.

Best,

Fernando V.

[PiersM]

Certainly Fernando - here you go

Piers

[fmvizcaino]

Hi @PiersM ,

I would suggest you to create the quarter column as I'm showing in my example. It will be easier that using the multi-row tool, especially in those cases where you are in the middle of the quarter and  there isn't available quarter data.

Best,

Fernando Vizcaino

[PiersM]

Great - thanks Fernando - 

So instead of using multi row formula, which tool should I use?

Best

Piers

[fmvizcaino · Accepted solution]

@PiersM ,

Now we can use that idea with the summarize + join tool.

See if this is the result you are looking for.

Best,

FErnando Vizcaino

[PiersM]

Thanks Fernando - 

You've helped me create a workflow so that now I can analyse my data between monthly and quarterly periods as well as being able to choose which data sources and categories of data to bring into the analysis .... this is so cool!!!!

Really appreciate your help,

Piers