Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to get row total

Highlighted
5 - Atom

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.  

Highlighted
Moderator
Moderator

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!

Highlighted
14 - Magnetar

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:

 

grossal_0-1585773453980.png

 

 

Output:

 

grossal_1-1585773479169.png

 

 

 

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

 

 

 

 

Highlighted
Alteryx
Alteryx

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

Highlighted
14 - Magnetar

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?

 

 

Highlighted
Alteryx
Alteryx

@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.

 

Capture.PNG

Highlighted
14 - Magnetar

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.

Labels