topic Re: Creating Quarterly Totals from Monthly Data in Alteryx Designer Desktop Discussions

Creating Quarterly Totals from Monthly Data

PiersM — Mon, 06 Apr 2020 23:45:19 GMT

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

Re: Creating Quarterly Totals from Monthly Data

fmvizcaino — Mon, 06 Apr 2020 23:59:52 GMT

Hi @PiersM ,

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

Best,

Fernando Vizcaino

Re: Creating Quarterly Totals from Monthly Data

AbhilashR — Tue, 07 Apr 2020 00:05:22 GMT

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.

Re: Creating Quarterly Totals from Monthly Data

PiersM — Tue, 07 Apr 2020 00:23:34 GMT

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

Re: Creating Quarterly Totals from Monthly Data

fmvizcaino — Tue, 07 Apr 2020 00:28:15 GMT

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.

Re: Creating Quarterly Totals from Monthly Data

PiersM — Tue, 07 Apr 2020 00:47:38 GMT

Certainly Fernando - here you go

Piers

Re: Creating Quarterly Totals from Monthly Data

fmvizcaino — Tue, 07 Apr 2020 00:56:44 GMT

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

Re: Creating Quarterly Totals from Monthly Data

PiersM — Tue, 07 Apr 2020 01:49:30 GMT

Great - thanks Fernando -

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

Best

Piers

Re: Creating Quarterly Totals from Monthly Data

fmvizcaino — Tue, 07 Apr 2020 01:56:42 GMT

@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

Re: Creating Quarterly Totals from Monthly Data

PiersM — Tue, 07 Apr 2020 02:56:56 GMT

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