
1. Re: Help with stacked bars
Benjamin Greene Oct 25, 2016 12:06 PM (in response to Matt Hong)The problem you were having was because of how your data was formatted. Since the Future monthly bill values for a certain person were not in the same row in the raw data as that person's Current monthly bill value, simply subtracting one from another would not work.
To remedy this, I edited both of the above calculated fields to be Level of Detail expressions (LODs) so that we can compare a person's Current monthly bill with their Future monthly bill. Finally, I edited the Change direction calculated field so that it takes into account that "Don't Know" case you mentioned. Finally, I built the view. Let me know if you are having any trouble replicating this.

helpwithpercents.twbx 43.4 KB


2. Re: Help with stacked bars
Michel Caissie Oct 25, 2016 12:16 PM (in response to Matt Hong)Matt,
If you check in the attached, on the sheet validate data.
You can see that rows having a Current Monthly have a null for Future monthly and vice versa. So the Change always returns a null .
So what you want is for every Person to have the Current monthly and Future monthly values on every row. This can be done using lod calculations
like this;
{FIXED [Group],[Person]:SUM( if [Pivot Field Names] == 'Q23' then [Pivot Field Values] end )}
Here for every row, you get the Q23 value of the group GroupPerson
Do the same for the Q38 and now you have values on every row to compute the Change direction.
Also you don't need to int the Pivot Field Values since it's already an int.
And finally, since you can have more than one record for each grouppersonpivotField, and depending on your business logic, you may have to change the SUM aggregation in the above calculations for whatever you need.
Michel

helpwithpercents ed mc.twbx 29.4 KB


3. Re: Help with stacked bars
Matt Hong Oct 25, 2016 12:40 PM (in response to Benjamin Greene)I figured that was the problem, but couldn't get my head wrapped around how to address it.
Why does the LOD fix it? Seems like a very easy solution, but what is the difference in this case between the normal agg. and the LOD agg.?

4. Re: Help with stacked bars
Michel Caissie Oct 25, 2016 12:59 PM (in response to Matt Hong)1 of 1 people found this helpfulWell I guessed, based on your Sheet 52, that you want to compute this for every PersonGroup.
Aggregations are based on the level of detail of the view.
And since you don't have Person in the Detail of the View it would not be possible the get the right numbers.
Using LOD, you can fix the level of detail of the calculation directly in the calculation.

5. Re: Help with stacked bars
Benjamin Greene Oct 25, 2016 1:19 PM (in response to Matt Hong)2 of 2 people found this helpfulTo understand the difference, let's look at 3 different, but similar ways to specify a simple subtraction.
1. X  Y
With calculations like this (no aggregation functions included in the formula) Tableau will go into the raw data and look directly at the value for X and compare it to the value for Y in that very same row. This is no different than making a new column in excel with a formula of =A1B1, as Tableau will write a value for every single row in your raw data. This is how you had the formula written originally, and since there were no rows where both X and Y were present, you got all null values.
2. SUM(X)SUM(Y)
With calculations like this (normal aggregation formulas), Tableau will sum all of the X values within whatever dimensions you have specified in your visualization, and then sum all the Y values within those dimensions, then take the difference. This works to compare a person's X value and Y value, even if those values are not in the same row in the raw data. However, the drawback is that you would need to make sure that a Person dimension was dragged into the view, so that Tableau knows at which level to perform the SUM. In your case, your dimension was Group, so Tableau would have calculated this difference at the Group level, rather than the Person level.
3. {FIXED Person : SUM(X)}{FIXED Person : SUM(Y)}
LODs like this are powerful, because they allow you to aggregate measures at whatever granularity you desire, regardless of the dimensions in your visualization. Even better, the values they return are treated as unaggregated numbers that can be further summed or averaged. Also, as Michel pointed out, Tableau essentially "writes" the same value to every single row in the raw data within the dimensions you specified. So, for instance, this calculation will sum all the X values for a given person, then write that number to every one of that person's rows in the raw data. It will do the same for each person's Y values, so even if a person does not have his raw X and Y values in the same row, this calculation will allow you to subtract them.
I hope this makes sense. The biggest hill to get over is understanding how Tableau thinks and where it performs calculations and then writes the values to. Once you wrap your head around all that, everything really starts to open up for you.