5 Replies Latest reply on Sep 13, 2014 11:48 AM by Kyle Pipkins

# What-if analysis: How can I manipulate count of records by types with parameters?

Hello Tableau Community,

I have been at this problem but cannot solve it.

The data contains three types of records: "N", "U" and "V". Each of these types have an associated rate with them (rate column/dimension).

I want to do some sensitivity analysis based on changing the counts for each type of variable - that's why the parameters are set up. There are no real bounds how much each can change.

The goal would be to see the cost change as the different types are rearranged.

• ###### 1. Re: What-if analysis: How can I manipulate count of records by types with parameters?

Gergely,

I'm not sure I fully understand what you are saying, but I think all you need to do is create a calculated field for the adjusted Rate. For example:

IF [Type] = 'N' THEN [Rate]*(1+[Type N Change]/100)

ELSEIF [Type] = 'U' THEN [Rate]*(1+[Type U Change]/100)

ELSEIF [Type] = 'V' THEN [Rate]*(1+[Type V Change]/100)

END

See attached workbook, as you change your parameter it changes the value of the adjusted rate.

1 of 1 people found this helpful
• ###### 2. Re: What-if analysis: How can I manipulate count of records by types with parameters?

Certainly a step in the right direction, I really appreciate your help.

Let me clarify.

The "Rate" measure is a cost measure, each type having a certain cost. The total cost then is the summary of the "Rate" field (for each individual ID).

I want to model how the total cost changes if we move things around. Think of "Type" as types of factories, and "Rate" as the individual operating cost for that factory.

So for example: There are 18 of Type N factories, each operating at the cost of \$50 (=\$900). I want to see what happens if we increase the number of factories to 19 (the correct answer would be: For type N, total cost is \$950), or reduce Type N factories to 18 (total cost, \$850).

I want to visualize the combination of the different types of factories, that's why I have the parameters set for both negative and positive values. So for example: let's reduce type N factories by 2, increase Type U by 1, and decrease Type V by 5.

Does this make more sense?

Thanks so much!

• ###### 3. Re: Re: What-if analysis: How can I manipulate count of records by types with parameters?

Try this twbx. If it is the desired solution I can walk through in more detail what I did, if you'd like. Just let me know.

• ###### 4. Re: What-if analysis: How can I manipulate count of records by types with parameters?

Hi Kyle -

I thought your post was really helpful.  I am trying to do something similar here where I want the user to select a time period first and then they can modify the values.  Can you walk me through how you got to this solution in your workbook?

Thanks!

Liz

• ###### 5. Re: What-if analysis: How can I manipulate count of records by types with parameters?

Hi Liz,

Had to go in and revisit the use case and solution, but it looks like all I did was pass the parameter into a calculation to see how changing its value impacts the end result. In this scenario we had 3 types of factories N, U and V. Each one had an associated rate and the measure we were interested in was the Rate * the Count of Factories (or Count of ID). The parameter was used to say, "what if we had less or more factories?". Keep in mind the way the data is structured, we already have an aggregate with Count of ID and Rate for each factory type. What we need is to see the Count of ID (let's say 10) plus or minus the change from the parameter. That is calculation number 1, (ID*). I used an IF statement but a CASE statement would work too.

Second calculation is finding the new rate. If we had 18 Type N, but our parameter says -3 then the new ID* should be 15. So the new rate should be ID* times the Rate. That calculation is done in Rate*.

Hope that helps.