10 Replies Latest reply on Jan 21, 2019 1:58 PM by Scott Schmeling

How to use a value from one member in another members calc

Good Morning,

Does anyone know a way to use a value from one member in another members Calculation.

For Example- I have 2 locations: A and B

Within those locations we create a calculation to be Member Day Rate (MBR:

Member Day Rate = (Member Days / Membership) *1000

For Location A-

I have a member day Rate of 168 (Calculated in Tableau)

I have Member Days of 37361

Membership of 221,191

Location B-

I have a membership of 147086

I want to be able to figure out how many member days I would need in location B to get the same Member Day Rate:

Formula would be

(Location A's MBR / 1000) * Location B's Membership

Any suggestions on how to store the MBR of Location A and use it in a calculation with Locations B's Membership would be very helpful.

We are using Tableau 10.5

Thank you.

• 1. Re: How to use a value from one member in another members calc

Clarification:

The Goal is the have location B reach the same Member Day Rate as Location A.

Thanks,

• 2. Re: How to use a value from one member in another members calc

Your data is over simplified here.

How do you know that you want to compare to member A if you have 100 members?  Depending on how you want to determine that, different solutions are available.

• 3. Re: How to use a value from one member in another members calc

For example, you may have the user set a parameter value to pick the comparison member.  Or you may want to compare against the longest member.  Or the oldest member.  Or the one right in front of him on the sheet.

Each would use a different way to pull the value to compare against.

• 4. Re: How to use a value from one member in another members calc

Hi Joe,

Yes I have 15 Members.  Location A is the variable. Location B will always be the same but the membership will change based on year and month.  I have updated it a little to include more members.

Location B is the home location we are trying to see what it will take to match the other locations Member Day Rate.

Thanks,.

• 5. Re: How to use a value from one member in another members calc

Location A will be a parameter, sorry should have clarified- just trying to keep it simple.

• 6. Re: How to use a value from one member in another members calc

(V 10.5 here)

Comparing against a parameter value is the easiest of them all.

See attached.  a FIXED LOD grabs the data you want, and in this case loads the value onto every row for you.  [Comparison day rate] collects the day rate for the location you select.  (Note:  I changed the parameter to a string so I can make an easy comparison in the calc.)

I displayed the value in the title.

Calculation1 just does the math you need.

Special note:  this FIXED LOD gets evaluated before any filters are executed.  So if you put [Location] on filters and eliminate "a" from the viz, you still get the data you need.

1 of 1 people found this helpful
• 7. Re: How to use a value from one member in another members calc

Hi Joe,

Thanks- that definitely got me to where I needed to be.  The final answer is:

Location Parameter

Comparison PDR

{ FIXED : SUM(IF [Location] = [Location (copy)] then [Member Day Rate] END) }

New Member Days to obtain that member DAy Rate:

([Comparison day rate]/1000) * [Membership]

• 8. Re: How to use a value from one member in another members calc

Hey Joe,

Sorry to bring this up again, guess I missed some important information when asking the question.

Is there a way to do this without an LOD?  I have 2 separate Data sources for the data.

Data Source 1 Contains all the Member Days.

Data Source 2 Contains all the Membership Data.

I tried combining them in Excel using a Join but it just explodes my data.  My initial Data is 1.6M Rows but when I Join It grows to over 10.0 M Rows.

Thanks in Advance and sorry didn't include from the beginning- was trying to simplify to make it easier on everyone plus our data can't be shared so was trying to make the additional data easier.

Thanks,

• 9. Re: How to use a value from one member in another members calc

Can you hack up a similar 2-source example, just like the original one you had, but put the relevant fields into the two sources?

One way or another we can do it, I'm sure, but I took a look at the old one and tried to imagine what would go where, and I just couldn't be sure I would separate it correctly.

(Also, let me know which one you would expect to be the primary source.)

• 10. Re: How to use a value from one member in another members calc

Hey Joe,

I'll work on that- but I decided to go a different route and allow them to enter the Member Day Rate they aspire to.  That will then tell them the Member Days they need.  It allows the end user more flexibility- as they are not just looking at the top on the market as well as kills the issue of not being able to use the LOD with a Blend.

Thanks for the help so far- it definitely got me on the right track.

Thanks,