
1. Re: Aligning axes for separate components of waterfall (walk) chart
Joe Oppelt Mar 26, 2020 9:57 AM (in response to Adam Brinkerhoff)Well you don't have a workbook attached, but you're going to have to calculate that table calc in all 3 charts to find the max and min of the three charts' values for the reference lines.

2. Re: Aligning axes for separate components of waterfall (walk) chart
Adam Brinkerhoff Mar 26, 2020 11:32 AM (in response to Joe Oppelt)Sorry. Here's the workbook.

sample file.twbx 70.3 KB


3. Re: Aligning axes for separate components of waterfall (walk) chart
Joe Oppelt Mar 29, 2020 6:52 PM (in response to Adam Brinkerhoff)Sorry for the delay.
See attached.
I made a MAX and MIN calc for your Calc1 on the middle sheet. And its displayed in the title.
Next, look at the third sheet. I needed to put Product Group onto the Detail shelf so that we had the Calc1 values by Product Group like you have on the middle sheet. But instead of going with TABLE(across) (which is the default, and which works on the middle sheet), I set the table calc to run along Product Group:
But notice the numbers (especially the MIN) don't match what we see on the middle sheet.
Look at the first sheet. Here I DO have the correct numbers in the title.
In the table calc settings there is a pulldown for Nested Calcs. Calculation1 is nested within those MIN and MAX calcs. THis one also needs to run along Product Group.
So when we have both calcs running along Product Group, we get the right value for MIN/MAX of the table calc. You can also put the calc from sheet 1 onto sheet 3, and vide versa. Thus you can have all three values (actually 4 values since there is a MIN and a MAX for the middle sheet.). And all these together can let you create the same MIN and MAX reference lines on all 3 sheets. I leave the rest of the exercise for you to complete.

sample file_v2018.1.twbx 71.6 KB


4. Re: Aligning axes for separate components of waterfall (walk) chart
Adam Brinkerhoff Mar 30, 2020 11:03 AM (in response to Joe Oppelt)Joe
This is great! Just one more thing, and I think it'll be perfect. When I create the MIN and MAX reference lines, the values are 10x the value shown in the title (see below). Any idea on how to correct that so that they are the same? It seems like it's multiplying the MIN and MAX amounts by 10, based on the 10 products in the Product Group dimension.

5. Re: Aligning axes for separate components of waterfall (walk) chart
Joe Oppelt Mar 30, 2020 11:11 AM (in response to Adam Brinkerhoff)I'd need to see your calc in your latest workbook.
Otherwise I can just guess and tell you to divide by 10. 
6. Re: Aligning axes for separate components of waterfall (walk) chart
Joe Oppelt Mar 30, 2020 11:52 AM (in response to Joe Oppelt)Well, wait. I added the calcs in the workbook I uploaded yesterday.
See attached.
Will your first and last sheets always be using the same product group? I had to put IFlogic in my MIN MIN and MAX MAX calcs to look only at them to get the MIN and MAX calcs to work.But I get the same MIN and MAX on the first two sheets in the attached.

sample file_v2018.1 A.twbx 72.6 KB


7. Re: Aligning axes for separate components of waterfall (walk) chart
Adam Brinkerhoff Mar 30, 2020 1:06 PM (in response to Joe Oppelt)Ok. That was easy. I've set the reference lines at the MIN and MAX values, then I unchecked the "Include zero" box in the Edit Axis window. Problem is, since we added Product Group to the table calculation the graph now has the individual product components in the stacked column. My hope was that when I excluded zeros, the min of the axis would be based on the MIN value your formula produced, and the max value of the axis would reflect the MAX formula, but it doesn't. I am able to exclude zeros and reset axis values based on reference line values in the middle chart, just not the end charts. I wonder if I'm looking for something that isn't technically possible. Here's the current work product.

sample file_v2018.1.1.twbx 78.1 KB


8. Re: Aligning axes for separate components of waterfall (walk) chart
Joe Oppelt Mar 30, 2020 3:00 PM (in response to Adam Brinkerhoff)I have a bit of questions about your data. Here is a screen shot of a piece of your Actual sheet. I turned the axis back on. On that sheet I don't get the compartmentalization by Product Group, so apparently on that one only one Product gets data. But Look at this screen shot:
The axis shows a value of 187, but the tooltip shows 49. Not sure what you really expect to be seeing there.
In the previous iteration of your data the OP sheet also had only Product 1. (That's why I asked if those two sheets will always use the same product. But your new data tells me that's not the case.)On the OP sheet you do have multiple Products in there, which is what you raised to me in your last reply. To deal with this we need a new way to display things. (You still need Product on the sheet to recalc what we get from the variance sheet. ) And since that compartmentalizes your bar, here is what I did:
First, when I display the axis, I see the sum of all of the products is 191. And if I take Product off the sheet, the overall sum totals to 191. So that's what I'm aiming for. (Or at least I hope so!) I made a window_sum calc to sum up all the products. When I displayed that in TEXT, I got a total of 191 displayed on each Product partition. (I made the same for Actual, but if you have only one product ever, this is superfluous.) I also made my own max and min for the variance sheet. I didn't understand the extra math you were doing in the old calcs I had done for this.
In the title of the OP sheet, I have displayed all those values.
Now that the WINDOW_SUM is getting displayed uniformly in all the product partitions, I really only need to display the first one. Look at the INDEX calc I put on filters. This says to display only the first product.
(Special note: Now each and every table calc is getting run along Product on this sheet.)
Finally I replaced the calc on ROWS to display the table calc.
And then I made my own MAX MAX calc to grab the max of the three sheets. On the OP sheet now, we have a bar displaying 191, and we have a reference line for MAX that is 193.
THe same exercise for MIN MIN would arrive at 187. (But I wonder if we really need that. The only way it would be important is if numbers could go negative.
See attached

sample file_v2018.1.1 B.twbx 81.1 KB
