how to add values with dynamic columns

[wzhu717]

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? 

[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 

[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"?

[TomWelgemoed · Accepted solution]

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

1. 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
2. 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

[danilang · Accepted solution]

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

[wzhu717]

this is what i am looking for. thank you .

[wzhu717]

this is very helpful, thank you Tom.