Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to get row total

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

6 REPLIES 6
ianwi
Alteryx Alumni (Retired)

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!

grossal
15 - Aurora
15 - Aurora

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

 

 

 

 

echuong1
Alteryx Alumni (Retired)

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
15 - Aurora
15 - Aurora

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
Alteryx Alumni (Retired)

@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

grossal
15 - Aurora
15 - Aurora

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