10 Replies Latest reply on May 6, 2016 1:15 PM by Phillip Overpeck

Scatter Plot (formula help)

Hello all,

This question is probably directed to all the data viz wizards out there.   This may be an easy question but I have been trying for about a week without any success.

What I am trying to accomplish is to create a scatter plot that displays the  year 2015 values for Revenues and Ending Balance at year end.

On the vertical y-axis, I would like to see Ending Balance (at year end - December's balance) and on the horizontal x-axis total Revenues for the full year (sum of all revenues for the year).

The issue that I am having is that for Ending Balance, the value should only show the December 2015 amount, AND NOT give me the total SUM  of all the monthly values for the year.  Since it is an ending balance, it should only reflect the balance at the end of the period.

ANY HELP IN THIS WOULD BE GREATLY APPRECIATED!~ !!!!!

I have attached a workbook with the sample data and scatter plot.

THANK YOU ALL!

• 1. Re: Scatter Plot (formula help)

Dario!

See attached. You can utilize LOD expression to grab the last month's value for each company and year by creating a custom year date from your date value.

{ fixed [Date (Years)], [Company] : sum(if month([Date]) = 12 then [Ending Balance] end )}

1 of 1 people found this helpful
• 2. Re: Scatter Plot (formula help)

You should be able to use this calculation to pull out just December ending balance

IF MONTH([Date])=12 THEN [Ending Balance] END

1 of 1 people found this helpful
• 3. Re: Scatter Plot (formula help)

True, you could do that without LODs too! Phillip's solution is better!

• 4. Re: Scatter Plot (formula help)

THANK YOU !!!  That was incredibly fast Phillip and very helpful!

• 5. Re: Scatter Plot (formula help)

Pooja,

Thank you for being so quick to answer and such a great ambassador for Tableau..   The learning curve has been steep for me but the help from people like you who take the time to answer is truly appreciated!   If I see you at Data16 in Austin, I will thank you personally

• 6. Re: Scatter Plot (formula help)

Happy to help, Dario! Thanks for the compliments. And yes, I would love to meet you at TC16

• 7. Re: Scatter Plot (formula help)

Pooja Gandhi

Phillip  / Pooja,

I have one additional question to add flexibility to that workbook you both helped me out with.   I know in theory it is possible, just don't know how to do it, but is there a simple easy way to add a Parameter  that will give the user to select the period (month &year) and have it update automatically ?

For example, from a drop down menu select July 2014, and return back the following values:

[End of Balance]  will return July 2014's value.

and [REVENUES] will return the YTD values of July 2014 (the Sum of the first 7 months of 2014's revenue.

Thank you again.  Hope I'm not getting too greedy asking for the additional help.

• 8. Re: Scatter Plot (formula help)

Hey Dario!

Yes, you can create 2 calcs for ending bal and revenue.

Ending Bal:

if year([Date]) = year([Date Parameter])

and month([Date]) = month([Date Parameter]) then [Ending Balance] end

Rev Calc:

if year([Date]) = year([Date Parameter])

and month([Date]) <= month([Date Parameter]) then [Revenues] end

But you can not dynamically have parameters update with new values, so you will need to manual refresh values when your data updates.

See attached.

1 of 1 people found this helpful
• 9. Re: Scatter Plot (formula help)

Ok. Take a look at this.

I created a parameter based on date and formatted it to display as Month - Year

Then I created a calculated field for the ending balance as:

IF MONTH([Date])=MONTH([Month Date Parameter]) THEN [Ending Balance] END

Next I created a calculated field to filter the dates based on the parameter selection:

IF YEAR([Date]) = YEAR([Month Date Parameter]) THEN "Show" ELSE "Hide" END

Let me know if this is along the lines of what you were thinking

1 of 1 people found this helpful
• 10. Re: Scatter Plot (formula help)

Wow you guys are awesome, Phillip and Pooja Gandhi !

Going to give both of your solutions a check to see which works best for what I'm trying to do... thank you again!