Is there a way to create X number of columns for a row performing calculations?

[tshivers]

I'm trying to recreate an excel file in Alteryx to minimize potential errors. For part of the file, I need to recreate ~14 columns (Month, Month+1, Month+2,..., Month+14) for 5 Different Business areas (trying to recreate image 2) 

Example of what my excel looks like: 

These tables are repeated for the 5 business areas that I need to focus on.

Essentially (as shown in the coral row, picture 1) each of the columns is using the same hit rate, multiplied by a different volume (pulled from a different sheet in the excel). In the second image, there's a calculation to determine if the date is less than or greater than the date of the column, and based on that, what value to use from the first image. 

How can this be simplified? I'm trying not to just recreate each column individually, if possible. But I understand I might have to. 

Thanks

[gautiergodard]

@tshivers Im not sure you would be able to dynamically create new columns.

However, what you could do is use a generate row tool to generate the months you need, and then cross tab those months into columns.

[JamesCharnley]

Hi @tshivers

Not sure I'm 100% on the right lines here but I'll give it a shot. Whenever you're going to want to create a lot of columns, the cross tab tool will be your friend at some point. I'm providing just the base level of logic here, but what I'm doing is generating the dates that I want as fields with the Generate Rows tool based on the first month I'd have data for (could pull with a summarize for example) with the DateTimeAdd function, then cross tabbing the months to be their own columns. I believe that in your actual workflow you'll be able to populate Headers with the dates, and values will be the calculated values that will have needed to be calculated already.

Let me know if that's not on the right lines.

[binuacs · Accepted solution]

@tshivers One way of doing this