how to add values with dynamic columns

i have ran into a little challenge, So I have a set of values and I cross tab these values so they becomes the title of the columns, but sometimes there are additional values in the list so when I cross tab them, it creates more columns, then when I put the formula tool in, it is missing those new values. If I add them in the formula tool when it doesn’t happen it will creates an error. Is there a way to put in the formula tool so it recognizes the new created columns?

Example.yxmd

Workflow

Accepted answers

TomWelgemoed

Hi @wzhu717 ,

I really hope this provides answers to both your questions:

1. Using a dynamic calculation - there are multiple options. I add 2 that I can think of in the attached packaged workflow (the one mentioned yesterday was a 3rd option).

Use a join to only use the fields you're interested in. I've used your own data. Let's say this month the calculation should only include apples & bananas - put that in a 2nd input tool and the join will automatically only sum them. You can still join the non-matching values if you'd like as an extension of this example
Use a dynamic formula (also part of Crew Macros). I've attached an example for you in the same workflow - it works on a similar principle in that you need to supply which fields needs to be considered. But it allows you to keep your "rule" separate and update it based on conditions (e.g. in February use this calculation, in March this one etc.)

2. On the Ensure fields question, I've added this in the workflow too. See the image below - it highlights how you could always "Ensure" that certain fields are present ... in the event that you need them for a calculation further down the workflow. Simply list them and choose the relevant data type (in this case Int).

Hope that does it! 🙂

Regards,

Tom

Ensure fields.png

Example (3).yxzp

danilang

Hi @wzhu717

Here's a modification that allows you to specify which fruits you want to sum.

The Fruits to sum contains a list of the Fruits that you want to total. The Find Replace tool adds a new field to the input data if the fruit is in the list of fruits to sum. The filter takes only only the records where this new field is not null and generates a total of only these fruits. The total is appended to the cross tabbed(No total column) input giving you

I used a Text Input for the list of fruits to sum, but you can replace this with an input data tool that reads an excel file or a List Box interface tool

Dan

FilteredSum.yxmd

All comments

TomWelgemoed

Hi @wzhu717 ,

I believe what you're looking for is the "Ensure Fields" macro, which is included in the Crew Macros pack, which you can download from here: http://www.chaosreignswithin.com/p/macros.html. It's a perfectly reliable set of macros created by Alteryx developers themselves.

After installation, simply add the Ensure fields macro prior to your cross-tab and it will "Ensure" the field(s) are present. You can simply select the Text option and write as many lines as you will have columns (each on it's own line).

Hope that helps.

Tom

ScottE

Hi @wzhu717

Are you trying to get your "Total" formula to update based on how ever many fields, or in this case fruits, are added?

In the Crosstab aggregating options you can add a Total Column and you won't need a formula at all.

Hope this helps.

wzhu717

Hi Tom,

thanks, how exactly do i use this tool? isnt it just creating a new column base on what name i put in? (same as the formula tool?) unless this tool checks if the excising columns in the workbook then if that column is not there then it creates a column?

wzhu717

hi,

not really, i am just using that as an example, but the data i am using sometimes there are 4 fruits and sometimes there are 6 fruits. so i have add the new fruit into the formula tool each time when there is a new fruit. i am looking for a dynamic way to add the fruits together without typing in or deleting fruits from the formula tool.

danilang

Hi @wzhu717

You can do this all without using a formula at all by leveraging the Total column in the Cross Tab tool.

The workflow looks like this

Both of the Crosstabs have the Sum and Total column options checked. After the union, the Dynamic Rename removes the "Sum_" prefix and the Select moves the Total column to the end, resulting in

Since there's no formula tool, it will work for any input columns

Dan

SumNoFormula.yxmd

wzhu717

Thanks, but what if i only need the sum for "Apple" + "Pear" +"Mellon"? and that can change every month, next month i would need to sum "Grape" + Peach"+"Pear"?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703