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.

Replicating excels what if analysis Data table

Rafiq123
5 - Atom

Hi 

I am new to Alteryx and want to replicate excel’s Data table function(what if analysis) in alteryx. Is there a way to do it? 

2 REPLIES 2
jamielaird
14 - Magnetar

Hi @Rafiq123 ,

 

Interesting challenge. Short answer: yes.

 

To demonstrate this, I had a go at this using this example of a What If scenario using the Data Table feature in Excel.

 

In Excel, the output looks like this. Before running the What If > Data Table analysis, the user would need to create a table with the range of interest rate and term values to be evaluated, and then select the Interest Rate and Term cells when initiating the Data Table.

 

jamielaird_0-1615912975746.png

I developed the attached app which achieves a similar end result in Alteryx, with a bit more flexibility for the end user. When opening the app the user sees an interface where they can set the values of the Principal, Interest Rate and Term:

 

jamielaird_5-1615913135051.png

 

Then the user defines the range of scenarios for the two variables (Interest Rate and Term), in both cases inputting a Min, Max and Increment:

 

jamielaird_6-1615913142809.png

 

jamielaird_7-1615913149697.png

The Alteryx app output provides the same numbers as the Excel version, albeit with a few formatting differences which could easily be tidied up:

 

jamielaird_8-1615913180961.png

 

The huge advantage of this type of approach in Alteryx versus Excel is the solution is much more dynamic for the end user. As well as providing the calculations, Alteryx will build the data table for whatever range of scenarios you allow.

 

The underlying workflow looks like this - the key part being the calculation, which is currently hardcoded for this scenario, but could be varied. This is one area where I could use a deeper understanding of how Data Tables work in Excel, but I believe the underlying calculation here is consistent (and the results obviously match).

 

jamielaird_9-1615913361761.png

 

 

Think of this as a rough proof-of-concept which you can take and build upon for your own particular requirements.

 

 

 

Rafiq123
5 - Atom

Hi

thank you for your response 

i actually understood what s happening in this workflow but by any chance can you upload the workflow as i want to see what configurations you have used specifically in all the tools 

i dont know what and how to use the tools particularly. If you can help me with that. 
thank you 

Labels