Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Use Excel column field as formula definition for grouping

unityindigopwc
5 - Atom

I'm trying to use Alteryx to do benchmarking. I have a series of facilities with survey questions and answers, and I want to compute metrics within the same facility for all facilities. There are about 130 different metrics. I'm also working with a prior analyst's work, where the metric definitions are next to each metric number on a different table. A toy example is below.

 

My thoughts on my options: 

  1. Find a way to perform these calculations (all within the same facility as a grouping) where I can use the metric definition (from excel ideally) as input as the metric definitions as a custom formula input.
  2. Re-define all these metric calculations within Alteryx, although I'm not sure the best way to do that without creating a giant workflow. 

I welcome any thoughts on the right tool for the job. I can use Qlik or Excel also.

 

FacilityQuestionAnswerMetricCalc
F1Q145M111.25
F1Q24M232
F1Q38M31440
F2Q142M17
F2Q26M242
F2Q37M31764

 

MetricDefinition
M1Q1/Q2
M2Q2*Q3
M3Q1*Q2*Q3
4 REPLIES 4
kcgreen
8 - Asteroid

I don't know that there's an easy way to create 130 formulas.  Here's how I'd do it one by one:

 

Capture.JPG

kcgreen
8 - Asteroid

I just thought of a way.

 

You can edit the Alteryx worklow in Notepad.  I'd create a line of code for each formula in Excel then paste that into the XML version of your Alteryx workflow.

 

 

capture3.JPG

Claje
14 - Magnetar

There's a really powerful CReW Macro called Dynamic Formula that is perfect for your workflow.


I've attached an example of how to build your desired output.

 

The key requirement is defining the data type of your metrics, and the data size.  I used Double with size 8 as a default, but you may want FixedDecimal with a specific size instead.  This is really up to you and depends on your granularity//accuracy requirements.

 

Hope this helps!

unityindigopwc
5 - Atom

This tool is great thank you. It will take me a bit to learn and translate back to my actual data set but this is an awesome start. 

Thank you both for offering your help!

 

Edit: I got the macro plugin working and it's perfect for what I need. This way I can change the metrics in an excel spreadsheet and just use those, but I will use the first response as a backup if things get hairy! Thanks again!

Labels