13 Replies Latest reply on Oct 5, 2016 8:59 AM by Robert Prince

# Lookup function on calculated fields

Hi,

I have a small attached workbook in which I need to create a calculated field that compares the "Percent of Population" field in the left pane to the "Percent of Population" in the right pane.

For example, on the African-American line, it should be 3.47% / 1.48% for a resulting value of 2.34.

I'm trying to use the lookup function but I can't make it work.

Thanks!

• ###### 1. Re: Lookup function on calculated fields

It depends on what you're actually looking to do in there.

I added stuff to do it two different ways.  The first was to capture the data in separate calcs if the STATE was "CA" or if the STATE was "CO".  then I did a table calc to divide the two.  (See [Calc 1].)

But then I also used LOOKUP to look back one state and just grab the value from the previous state, and divide it by the current state.  (See [Calc 2].)

And the example begs some questions:  are you always just going to have two states?  Will you always be comparing against CA?  Depending on what you are really looking to do, there might be an even better way to do it.  these are just quick and dirty examples.

• ###### 2. Re: Lookup function on calculated fields

Thanks, Joe.

The problem is that I need it to be the same structure as before with the states across the top and the Profile Variables

down the side (there are many more variables that I omitted.)

And to your questions, there would be more than these 2 states and the order could change, so I wanted a dynamic

table that could handle this calculation.

Is it possible?

Bob

• ###### 3. Re: Lookup function on calculated fields

I'm struggling to wrap my head around what you need.

So imagine there are 50 states.  You want to divide a number from one state by a number from another state.  How do you know which state to make the numerator and which state to make the denominator?

• ###### 4. Re: Lookup function on calculated fields

The workbook will contain 50 states' worth of data, but you will filter to only show 2.

The calculation will be [state to the left] / [state to the right].

Does that help?

• ###### 5. Re: Lookup function on calculated fields

Then the method I showed using [Calculation2] should take care of what you need.

1 of 1 people found this helpful
• ###### 6. Re: Lookup function on calculated fields

But it doesn't.    I've tried it and can't make it work.   Can you try?

• ###### 7. Re: Lookup function on calculated fields

On sheet 14 of the edited workbook I uploaded yesterday, Calculation2 divides the value from CA by the value from CO.

What else are you looking for me to try?

1 of 1 people found this helpful
• ###### 8. Re: Lookup function on calculated fields

Yes it works in the structure you created (with States in the rows and Profile Variable/Category in the columns),

but the structure I need to use is the one I originally attached, with the States as Columns and Profile Variable/Category as rows.

When I switch your workbook back to the original structure, and use Calculation1, it doesn't calculate it accurately.

Thanks.

• ###### 9. Re: Lookup function on calculated fields

Hi Robert,

I've attached a packaged workbook that I believe has the functionality you're after.

Hope that helps,

Ryan.

EDIT: Simplified calc.

1 of 1 people found this helpful
• ###### 10. Re: Lookup function on calculated fields

Thanks, Ryan.   And thanks Joe for helping me think through the problem.

• ###### 11. Re: Lookup function on calculated fields

Robert -- Do you know what Ryan did to make that work?

At the top of your editor is a "flip this sheet" icon that swaps the direction of the sheet. That puts the sheet back to the original orientation.  And then he just deleted out the other calcs.

It's just a matter of cosmetics.

• ###### 12. Re: Lookup function on calculated fields

Not wanting to sound confrontational as I'm pretty sure I could've done that if I'd downloaded your workbook instead of Robert's - but like Julie Andrew's said, "Let's start at the very beginning, a very good place to start", I downloaded Robert's workbook.

After looking at your attachment we actually came to the result using different methods too, I used LOOKUP

• ###### 13. Re: Lookup function on calculated fields

Hi Joe/Ryan,

I should backtrack and note that you both were correct, and when I simply swapped the columns and rows, Joe, your calculation also worked.

The reason it wouldn't work was that I was manually dragging the fields to rows and columns to rearrange them and when I did that the calculation

didn't work.   Very odd.   Ultimately what it came down to was that when I manually rearranged fields, the "Compute using" for the Index field (the field I was trying

to calculate) was dictating the compute using for the Total #Accts in Pop field, meaning when I set the Index field to compute using table across, it made the Total #Accts in Pop field compute using table across.

So the best solution was, in the Measure Values shelf, to Edit Table Calculation, then set the Index field to compute using table across, and ALSO switch to the Nested Calculation field of Total #Accts in Pop and set compute using Pane(down).

Thanks again for your help.