Creating Quarterly Totals from Monthly Data

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

Comtrade_Data_Example.yxmd

Accepted answers

fmvizcaino

@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

2020-04-06 Creating Quarterly Totals from Monthly Data.yxmd

All comments

fmvizcaino

Hi @PiersM ,

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

Best,

Fernando Vizcaino

Comtrade_Data_Example (1).yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703