Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Hi all, fairly new to Alteryx.
I have a set of data with thousands of Company names, each company name having multiple rows of data.
I currently have done a summarize and union function to create new columns that would basically give me a sub-total for each company.
Example:
Company 1 Data
Company 1 Data
Company 1 Data
Company 1 Data
Company 1 Sum Data
Company 2 Data
Company 2 Data
Company 2 Data
Company 2 Data
Company 2 Sum Data
I am using the multi-row formula function to fill in the 3rd column of data with the "Sum Data" value.
My Problem is the new data looks like this....
Example:
Company 1 Data
Company 1 Data
Company 1 Data
Company 1 Data Sum Data
Company 1 Sum Data
Company 2 Data
Company 2 Data
Company 2 Data
Company 2 Data Sum Data
Company 2 Sum Data
As you can see, it is only working for the row RIGHT ABOVE the summarize column value.
Any help is welcome!
Solved! Go to Solution.
The only way I know how to do this is to add a RecordID field, reverse sort using RecordID Descending, perform the forward fill, then resort using RecordID Ascending.
Reading through your scenario, I think you have two options here to achieve the visual subtotals you're looking for:
1. If formatting isn't as imperative, try some of the built-in tools that Alteryx uses for summarizing: the Summarize tool would allow you to group by company and then sum up the data (but you'd lose your detail, since it would just show one line with a total per company)... or the Running Total tool, where you can group by company and then show a running total per line. But keep in mind this would put the "subtotals" in the column next to the data, rather than in a new line at the bottom of each company...
2. If you need to force your data to appear in more or less the same format we're used to seeing when using programs like Excel (subtotals, pivot tables, etc.), then you have to get a bit more creative - Alteryx likes to put everything in very basic tables where each line is a record (or a subtotal, etc. depending on your workflow). So this makes the more "visually appealing" subtotals that we're all used to seeing in Excel/etc. a little trickier to display... Not impossible, however (few things are with this program, as I'm sure you will discover for yourself as you continue to explore!) So if you need it formatted this way, try something like the following to get your "subtotals"...
1. Add a new line to separate your companies using the Multi-Row tool + Generate Rows (this will be your future Subtotal line)
2. Remove unnecessary info & put NULL values for duplicate Data from the newly added subtotal row
3. Calculate Running Total, grouping by Company
4. Remove unnecessary info & put NULL values for every row except the last row per company (i.e. the new "subtotal" line).
There are a few different ways you could reach the same result for option #2 (I can think of several methods using tools like unions & record ID's), but the method above/attached might work for you. If not, let us know where we can tweak it!
Cheers,
NJ
