Dynamic Column Variances

[r_tripleus]

Hi,

I am creating a workflow to calculate variances for x number of days.

My column name and number of columns will vary based on number of days for analysis and dates used. 

Need help or suggestion on how to proceed

[mceleavey]

Hi @r_tripleus ,

Can you post some data and some more information of the variance you want.

Is it daily variance per row? Per total?

M.

[r_tripleus]

Thanks @MC_dup_196 ,

I have attached a sample output.

• Column headers currently are dates. There could be 2 days or may be up to 5 (dynamic number of columns)
• Have to cleanse the null values to zero in all the columns
• Do a day over day variance based on Sec_ID for the number of days

I have been trying to use dynamic & multi field function, but unable to address this.

[mceleavey]

Hi @r_tripleus ,

I've built this for you using a bit of transpose and crosstab magic:

This pivots the data and creates the variance, then smooshed together the variance and the values and pivots back:

Hope this helps.

M.

[r_tripleus]

Thanks @mceleavey 

My Designer doe not have one tool. May I request you to understand what this tool does in the above workflow.

[mceleavey · Accepted solution]

@r_tripleus ,

apologies, that's a tool I wrote. I've attached that now.

M

ps - if you're on an old version of Alteryx, just open the .yxmc version and save it to your macro folder.

[r_tripleus]

Thank you @mceleavey  I got the workflow running