I noticed part of your calculated field says:
([Probability Change] = 1
But how can Profitability Change equal a number when it can only result in "Down", "Up", and "No Change"?
Thanks for pointing that out. I updated the attachement to reflect "Up" or "Down" in the Probability Change calculation. Tableau did not save this because I updated this from the 1 and -1 values after running into the error.
I found out where the error lies: the problem now is no longer with your Pipeline Stage calculation or your Probability Change calculation, but with your Start Probability and End Probability.
For example, when I removed the MAX() function from your Start Probability and End Probability, this happens:
Yes, this is true. But the problem I am having is that I am only able to get the correct Start Pipeline and End Pipeline by making them Aggregate functions. If you now look on Sheet 8 and re-insert Start, End, and Probability Change, the Probability Change is incorrect when comparing the Start and End Probabilities. If I could avoid making them an aggregate function, that would solve my problem.
1 of 1 people found this helpful
I took a quick look at this and very likely v9 will solve your problems with level of detail expressions, in the meantime I believe there is likely a workaround in v8 and earlier using table calculations. However I was unable to get at too much for details because there are so many red pills, I don't know what the intent is to be able to help you.
Here's a quick explanation of what I'm thinking. Prior to v9, Tableau had essentially 3 levels of calculation:
- record level. These fields are computed for each and every record.
- aggregate. These are computed for each and every mark, where the mark is based on the dimension(s) in the view, otherwise known as the "viz Level of Detail" or "vizLOD".
- table calculation, where table calculations can be nested to perform up to 128 additional levels of aggregation. These are computed for each and every mark, the results depend on the addressing and partitioning of the table calculation.
What it looks like you're trying to do is aggregate across _something_ to get correct Start Pipeline and End Pipeline, I'm guessing each PipelineID? Whatever that is, the way to express that intent in Tableau v8 and earlier is to build a view with the necessary dimension(s). Then if you need to do further aggregations across that you can use table calculations. The complication of table calculations is that the underlying aggregations may need to be at a coarser or finer granularity than the desired view, so there are various techniques required to deal with that since both table calculations and the regular aggregates are all computed in the same view.
What Tableau v9 adds is Level of Detail expressions, they let us define aggregate calculations that effectively compute outside the view and therefore keep the view simpler while the LOD expression lets us specify the desired granularity for each aggregate.
To help you further, we'd need to have a better sense of each level of aggregation you're trying to create.