11 Replies Latest reply on May 24, 2013 8:56 AM by Joe Mako

# How to Structure Data for What If Analysis

Folks-

I've attached an Excel workbook that shows roughly what I'm trying to accomplish in Tableau.

As you can see, the values for Green and Red are calcuated from the prior period's total, based on a rate, and an additional factor.

Additionally, I would like to break Green and Red into additional subcategories.

I've found that if I don't start with the right structure, I end up painting myself into a corner.

Any help would be appreciated.

Thanks,

RL

• ###### 1. Re: How to Structure Data for What If Analysis

You really only need three one fields: Year. The rest of the calculations can be done in Tableau. You'll want to create a parameters for all the first year's hard numbers and the growth rates (?). After that everything can be calculated in Tableau, or not.

--Shawn

• ###### 2. Re: How to Structure Data for What If Analysis

I suspected that might be the case. I'm unclear on what that parameter would look like however.

I can pretty much code any calculation, but I'm just not very in tune to the way Tableau works. What would my workbook look like? Just one initial value for each factor? If I want to modify those afterwards, do I even need a worksheet at all?

Also, how do I create a viz based on no data? Would I just have a list of years in my workbook?

I've made a few different viz's in Tableau but nothing like this.

Thanks,

RL

• ###### 3. Re: How to Structure Data for What If Analysis

Ralph Lindenfeld wrote:

I suspected that might be the case. I'm unclear on what that parameter would look like however.

I can pretty much code any calculation, but I'm just not very in tune to the way Tableau works. What would my workbook look like? Just one initial value for each factor? If I want to modify those afterwards, do I even need a worksheet at all?

Also, how do I create a viz based on no data? Would I just have a list of years in my workbook?

I've made a few different viz's in Tableau but nothing like this.

Thanks,

RL

What you're trying to do is rather interesting. I'm certain that Joe Mako could produce your needed workbook from thin air, really. You spreadsheet has just a few 'start' values in different columns. So my mention of parameters had to do with creating a parameter for each of the 'hard' values you start your spreadsheet off with. You only need to create parameters if you want to change them dynamically. If you're happy with hard-coding them into the viz then just put that value into a calculated field as a start point, and then create the calculations from there.

My whole point is your Excel calcs are easy to dupe in Tableau, so you don't really need a dataset to feed this worksheet at all -- except for year, which Joe can show you a scaffold solution for.

--Shawn

• ###### 4. Re: How to Structure Data for What If Analysis

I understand your point about recreating Excel calcs in Tableau- and I need to be able to do that, as this project requires me to provide "levers" for all of those calcs. That's why I'm crying for help now at the beginning of the project, rather than coding it in by hand, and then trying to figure out how to apply "factors" later.

So I've built parameters for starting values: InputStart, OutputStart; starting percentages: PctInputStart, PctOutputStart; and growth rate factors: GrowthPctInput, GrowthPctOutput.

How do I code a calc that says "for the first year, take the static inputStart value, and for each successive value take the prior period's value, and multiply it by (1+ (prior period's percentage+ growth factor))"- and get that to plot on the chart?

Yes, Joe Mako could do this I'm sure. I've attached a workbook.

Thanks,

Ralph

• ###### 5. Re: How to Structure Data for What If Analysis

--Shawn

• ###### 6. Re: How to Structure Data for What If Analysis
1 of 1 people found this helpful
• ###### 7. Re: How to Structure Data for What If Analysis

this is great- let me puzzle through the webex and I'll report back.

Thanks guys!

-RL

• ###### 8. Re: How to Structure Data for What If Analysis

Here is the workbook, please let me know if you have any questions.

• ###### 9. Re: How to Structure Data for What If Analysis

Wow- that's great, I'll dissect it right away. I was just half way through the webex and was just getting into parameterized years, but I'm going to switch gears..

Thanks,

RL

• ###### 10. Re: How to Structure Data for What If Analysis

This is really slick. I have two questions so far:

Why does In Rate use Lookup while Green use Previous_Value? Are you demonstrating two diff techniques or am I missing a significant difference between the two?

How does the Year Measure, which is just an Index() function, tie back into the Years Parameter, and the Year Value- I don't see them referring to each other in any calculations.

• ###### 11. Re: How to Structure Data for What If Analysis

Previous_Value is self referencing, using the result of the formula itself.

Lookup here is being used to shift all values down one mark.

These are very different operations, but I can see how they can appear to be similar.

The compute using (addressing) for all table calculations is the Bin field. The bin field is what is setting the increments with the bin size, and the range with the two values it is a bin of.