9 Replies Latest reply on Mar 20, 2013 7:12 AM by Carson B

# Parameters

Let me just start by saying that I'm not sure if this is even possible in Tableau...

But lets start with a more simple example of what I'm trying to do. In the spreadsheet you'll see that there are two different locations, and two different employee positions at those locations (plant manager, warehouse forklift). We can see how many employees are at each site, what the position's hourly wage is, total wages by position, and different measures of costs

In the workbook, I have started creating parameters and calculations for these variables

As an example, how would I create a workbook that shows a text table and parameters for the following? So, you could change the number of plant managers/warehouse forklifts and their wages (using parameter controls), and the total number of employees and total wage cost would vary accordingly:

# of plant managers

# of Warehouse Forklifts

Plant Manager wage

Warehouse wage

Total Employees

Total Wage Cost

The main point of the exercise is to allow someone to manipulate numbers using parameters controls. So, for example, using the parameter controls, what happens if we add two more plant managers to location 1 and another warehouse forklift to location 2, all while decreasing the hourly wage of plant manager at location 1 by .50 an hour? Keep in mind that the hour rates for the same position will vary across different locations, and that each location is its own entity - they have distinct costs, distinct positions and wages, etc. I imagine this means that there will need to be a different parameter for each variable at each location (Florida Location Position x, Florida Location Position x hourly wage, Florida energy cost, New Orleans location Position X, New Orleans Location Position x hourly wage)

In the end, and I say this in case it will help you understand the question, my hope is to model the current situation at multiple locations in regard to total labor costs broken down by position, total expenses, etc etc etc - basically a complete view at what owning location A costs. From that picture, you could then play with the total equation by making small changes to all of the variables. What happens if the energy costs here were lowered this much, I added four more plant managers and give them 2 dollar raises, and so on, eventually showing graphs and other visual displays in addition to the text table.

Please let me know if you have any questions, any ideas you guys have is most appreciated

• ###### 1. Re: Interactive Parameters and calculated fields

Hi Carson,

I usually go on the basis that everything is possible until the opposite is proven, but this could be a minefield.

How many things would you possibly amend at the same time? I think if it is everything it would quite easily become a nightmare of parameters - I do think you need a parameter for each value you want to amend. However if you limit the number of things to amend to say a number of locations at the same time, you might get away with it. For example: parameter one "choose first location" (which would allow you to pick any of your list), then the next few parameters amend details of that specific location. Then a second "choose location" parameter followed by some parameters to amend details of that specific location. There already you can choose 2 locations at random to amend details of. But event that approach should not be taken too far I think.

So question is... what is the limit?

Dana

• ###### 2. Re: Interactive Parameters and calculated fields

If I understand correctly, what you propose below would work – you would specify which location to alter, and then move to step 2. As long as we could make changes to multiple locations in the same dashboard. So, using your example, do you know how you would go about designing it?

Parameter 1:  Choose Location

Parameter 2: alter position 1 (ie plant manager) at selected location (both wage and number of employees in that position) to see impact on total cost – ie increase number of plant managers at location x by 2, and decrease wage by 1

Parameter 3: alter position 2 (ie forklift) at selected location (both wage and number of employees in that position) to see impact on total cost

Parameter 4: Choose location

… and this repeats for the second location

Final Parameter: Incentives (this would be a number. 10,000 \$ for example, which would then be subtracted from total cost)

The above would be perfect for now just to see how it works. Going forward I would want room for a little more but there would definitely be a limit.. say around 3 or 4 locations allowing for 5 or 6 different positions. The positions are all the same name, but from the data they are obviously paid different amounts at different locations. So, what if the limit was 3/4 locations and 5/6 different positions allowing the user to manipulate number of employees and their wages at each site? Then the same incentive parameter from above. We might introduce additional fields on top of this, but what I’ve outlined would be the only fields that the user could manipulate in this way

• ###### 3. Re: Interactive Parameters and calculated fields

It's quite simple.  Think of parameters as providing the user interface for each choice set they can make.  In between the viz and the parameter is the calculated field, which is behind each parameter selection.  A case statement in the calculated field returns the correct value for each parameter selected. You can place each calculated field on the worksheet and then sum etc as necessary..

• ###### 4. Re: Interactive Parameters and calculated fields

Hi Carson,

Attached is more or less what I had in mind, but like I said, it is a bit of a minefield as it might get very complex and annoying very quickly.

At least though it may give you some idea on how to play with the data ?

(oh yes I added some extra locations so you can see the differences between chosen and not chosen locations and the total cost minus incentives looks a bit odd being calculated for each position... that is due to the shape of your data )

Hope it helps,

Dana

1 of 1 people found this helpful
• ###### 5. Re: Interactive Parameters and calculated fields

Could you go into more detail or show an example of what you mean with the case statements John? I've never written one but found one in an example yesterday as I was doing research on this and it didn't strike me as what I was looking for. Here is an example I saw:

This example allowed the user to toggle between different fields in the data (region, category). It was actually pretty useful but how would you make the calculation include the user input I'm looking for? I want the user to be able add to or subtract from the variables. Where would the control be with the case statement to select different numbers etc?

• ###### 6. Re: Interactive Parameters and calculated fields

Thank you Dana - that is VERY helpful. I made some progress yesterday that was headed in the same direction but I got frustrated and took a break after just building 1 position, its wage, and their contribution to location cost. I was already getting confused and it seemed like eventually I might run into some circular cell references, for lack of a better term, because most of the equations referenced each other and so on and so on. I don't think I could have made it where you did so thank you again

I will take your advice and start playing with the data. By flat do you mean that I need more positions/costs/wages to make the locations make sense?

• ###### 7. Re: Interactive Parameters and calculated fields

ok,

Parameters:  (just 2)

name:  parm_Location

type: integer

North   1.25

East    1.35

South   .95

West    .90

-----

name:  parm_MgrWage

type text

entry   entry

journeyman   jnry

mid-level  mid

senior   snr

supervisor  sup

__________________________

Calculated fields

name: calc_Wage

case [parm_MgrWage]

when entry then \$15.60

when jnry  then \$22.50

when mid then \$27.25

when snr then \$30.90

when sup then \$35.00

else

0

end

Calc_LocationWage

case [parm_Location]

when 1.25   then calc_Wage * 1.25

when 1.35  then calc_Wage * 1.25

when .95  then  calc_Wage * .95

when .90  then calc_wage * .90

else

calc_wage

end

You can put as many parameter drop downs and calculated fields you want on the viz to pick the combinations

You can either put the calculated field on the row shelf or make a final calculated field that sums up the parameter driven ones.   We would have over 40 metrics of interest in our data set and by using this technique with parameters we could let our clients slice and dice the data anyway they'd like and build various reports on the fly.

1 of 1 people found this helpful
• ###### 8. Re: Interactive Parameters and calculated fields

Hi Carson,

I'm glad it is helping though I still think this could get very complicate hahaha

Still though it is fun to play with.

I've attached an example of both a new workbook and a new dataset (in excel) to demo what I mean by different data shape.

As you had it, the location costs were a little confusing as they were inseparable from the costs of wages/employees and you'd have to make really really sure that you would not fill in the costs at the level of plant manager AND fork lift because then suddenly your costs might double.

If you make your data more flat, you can really play with each bit of data, though naming becomes a bit generic. In my example, you can see Cost Types costs - being location specific values for energy etc. Sub type of cost will tell you what it is. Cost Type wages are number of employees and what you pay them. It makes the overview perhaps a bit neater and the calculations more easy to maintain.

Hope that helps

Dana

• ###### 9. Re: Interactive Parameters and calculated fields

I agree it is fun to play with! Now I just need to spend about 2 years trying to fully understand everything in your workbook

HUGE help thank you SO much!!!