I agree colors would make it more appealing and will likely do that if they choose to have visual input as well but my primary concern is not the graph, but how to I ascertain that break even comes in FY18-Q4 and how to I determine the number of quarters required to get to the break even point? Right now the request is to know how many quarters to BE and in which quarter BE will occur.
Considering some projects may not start for two or three quarters, I cannot use anything in regard to the current date, but from when the project will actually start.
Of course, being a beginner, I'm not sure how to add color like you did so nicely. Care to share?
Color can be added using the colors pill & they can be customized.
See the new calculation "BE' and place that on the Labels to display when BE occurred.
The problem is, in ur data Project quarter is a string, if u convert this column to be date type M/D/YYYY, then u could bring the min(Project quarter) to display exactly when BE occurred.
See the workbook, its ver. 9.3
TTBE Mockup.twbx 43.0 KB
I am on v9.2 (company standard at this time) and so cannot open the file. Is there any way you can save it in 9.2 format?
Thank you again Rahul.
What I am still after is how to identify the specific quarter in which BE occurs and the number of quarters it takes to reach BE.
These need to be values I can display on a dashboard. Any thoughts there. Graph looks much nicer now and you've helped me learn more how to utilize the tool.
BE occurs when previous value was negative & current value is positive.
Essentially u need to convert this logic into IF statement.
first condition checks whether previous value was negative
second condition checks whether current value is positive
both condition means its a BE
This is the first part of solution.
For the second part: number of quarters for BE to occur, this needs counting quarters.
Quarters can be counted when its date format.... strings cant be used to calculate counting.
Pls convert ur Projected QTR into date type and let me know.
TTBE Mockup.twbx 38.1 KB
Thanks again Rahul.
I've been working with the data and I can easily see when the BE occurs but being able to put on a dashboard/report that it will take X quarters and break even in Y quarter is a different issue. The visualization is great, extracting the data from the visualization is what I'm struggling with.
In the attached file I've got the Revenue vs. Break Even (revenue showing the discounted cash flow by quarter and BE showing the cumulative discounted cash flow - when does the cumulative cash flow turn positive). Using that it is easy to see that BE occurs FY18-Q4 and it takes 8 quarters to BE. However, how to I extract those two data items which are so easily seen?
I have somewhat of a stab at that in the BE QTR & Num QTRs sheet which has columns for the projected amount (discounted cash flow), cumulative projected amount, and also ranks the quarters in order and "highlights" the quarter in which BE occurs.
It is still essentially a visualization and I am still not sure if there is a calculation I can use which will tell me specifically the BE Quarter. Some of these items require going into advanced to make sure the table calculation is correct and so if anything changes about the table/chart, I end of having to redo those. I find that frustrating.
TTBE Mockup.twbx 39.7 KB
1 of 1 people found this helpful
Hey, i've just modified the BE logic as shown here:
Now based on this I see a visual cue on ur dashboard:
Now u wanna extract those values? What do u mean by that? do u want to see the underlying data like this:
Often I leverage most of the complex logic in the data , Tableau is not good at cross tabs and u especially if u want to extract an outlier based on a logic it has to include all the data to execute that computation which is eventually passed on to your Underlying Data Tab.
Another nice touch. That helps.
I guess what I had envisioned was to be able to have a summary line on a dashboard which would say for Project X the BE Quarter is Y and it will take Z quarters to reach BE.
Rather than a line, it could be a table simply showing the project, is current stage and the number of quarters required for BE and the actual quarter in which that project will BE.
Thus, my desire to be able to "extract" the number of quarters and the BE quarter.
You've been very helpful in helping me understand Tableau and how to do some nice things.
Number of Quarter comes through a Date Comparison.
for ex: A - B = delta
so if A is ur BE quarter then what is B ? is it current quarter? or is it an input from the user like a parameter in tableau?
1 of 1 people found this helpful
B is the quarter in which a project starts. Once there is cash flow associated with a project, it has begun.
The question of number of quarters to break even is "How many quarters will it take for this project to break even" as compared with "How many MORE quarters will it take for this project to break even". The second scenario would use system date or a user input date, etc. to determine but at this point, the only question is from the start of the project, how many quarters will it take to break even.
After playing with this, I'm thinking I'm probably better off doing some pre-aggregation in SQL rather than trying to do everything in Tableau.