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.
Solved! Go to Solution.
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!
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
There is a "Add Totals" macro available within the Crew Macro pack. It allows you to add both column and row totals.
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?
@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.
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.