How to get row total

TV & Radio sales is given for 24 months & for each unique shop.

I want to just get 2018, 2019 sum of TV & Radio sold (hence 4 new columns with 2018 TV sales, 2019 TV sales, 2018 Radio Sales, 2019 Radio sales) for each shop.

The Layman way is creating a formula by adding all 12 months in a year. But is there a better way to do the same.

Please help.

Thanks in advance.

Data - V1.xlsx

Accepted answers

ianwi

Hi @sudiptadutta87,

Your question asked if there was a better way. I wouldn't necessarily call this a better way, but it is a different approach. I Transposed the data so that I could use the Summarize tool to total by serial number and year/category. I then Cross Tabbed the results to make them easy to Join back to the original dataset.

Happy Alteryx-ing!

SalesSummary.yxzp

grossal

Hi @sudiptadutta87,

thanks for sharing some sample data. This helps a lot when trying to find out a way to solve it! Here is how it's done:

Output:

Let's explain what happens:

1) Transpose

Let's first shift all the data into rows, this helps to work with them, because we are more dynamic.

2) Filter

First of all, we need to split the "Headers" from the actual rows, so we filter for Radio Sales and TV Sales.

3) Data Cleansing

We want a clean data set, that's we remove leading/trailing whitespaces and numbers from the Transpose Tool.

4) Join

Let's bring the data back together, this helps us to bring the correct column names in front of the rows.

5) Formula

Let's get the year of all the dates.

6) Summarize

We build the sum grouped by year and type (radio/tv sales)

7) Formula

Your request contained the format "Year + Type", therefore we build a column with that information.

😎😎 Cross Tab

Let's bring everything into shape!

9) Filter

We got one false row with null values, let's make it clean 😉

Note: Without the double headers, it would be way simpler and you wouldn't need as much tools before the Year-Formula.

I'll attach the workflow. Let me know if I got it right!

Best

Alex

2020-04-01-row-total.yxmd

All comments

ianwi

Hi @sudiptadutta87,

Happy Alteryx-ing!

SalesSummary.yxzp

grossal

Hi @sudiptadutta87,

thanks for sharing some sample data. This helps a lot when trying to find out a way to solve it! Here is how it's done:

Output:

Let's explain what happens:

1) Transpose

Let's first shift all the data into rows, this helps to work with them, because we are more dynamic.

2) Filter

First of all, we need to split the "Headers" from the actual rows, so we filter for Radio Sales and TV Sales.

3) Data Cleansing

We want a clean data set, that's we remove leading/trailing whitespaces and numbers from the Transpose Tool.

4) Join

Let's bring the data back together, this helps us to bring the correct column names in front of the rows.

5) Formula

Let's get the year of all the dates.

6) Summarize

We build the sum grouped by year and type (radio/tv sales)

7) Formula

Your request contained the format "Year + Type", therefore we build a column with that information.

😎😎 Cross Tab

Let's bring everything into shape!

9) Filter

We got one false row with null values, let's make it clean 😉

Note: Without the double headers, it would be way simpler and you wouldn't need as much tools before the Year-Formula.

I'll attach the workflow. Let me know if I got it right!

Best

Alex

2020-04-01-row-total.yxmd

echuong1

There is a "Add Totals" macro available within the Crew Macro pack. It allows you to add both column and row totals.

http://www.chaosreignswithin.com/p/macros.html

grossal

Hi @echuong1,

I have seen that macro before, but haven't seen it in use. Would you mind sharing a solution to this post using the Add Total macro?

echuong1

@grossal Sure!

I probably over-complicated the workflow a bit, but here's my approach. You will need the Add totals Crew macro to run the last part.

Add totals.yxzp

grossal

Thanks for sharing @echuong1.

It sounded like it would be a more simple workflow, but I guess, the double header row is the real problem here.

On a note: I think you got the expected output wrong during the summarize.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335