Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Short of learning and thus writing MDX or, using CustomSQL with OpenQuery to pre-flatten the source (discussed here), this can be very problematic. 

Instructions

...

Resolution:

However, unlike many of Tableau's competitors, there us a fairly simple way around this problem but,  this can be process heavy, and you need to be very aware of the ordinal positions of your data as it appears in your view, and crucially, all amendments and enhancements to charts are definitely best done as a table before re-drawing as a chart.


Consider this image: assume for this example that we are looking at a cube output

Image Added

Let's imagine for a moment that you want to calculate the percentage difference between Sales and Forecast for Binders. Keep in mind that more often than not, measures are also partitioned by a dimension (Sales, Profit, Forecast), so it is often the case that you will be unable to refer to the measure-name too.

This is easy, we can perhaps write a calculation similar to this:

Code Block
languagesql
titleSales vs Forecast for Binders
(
    (If Min([Sub-Category]) =  'Binders' Then Sum(Sales) Else Null End)
    -
    (If Min([Sub-Category]) =  'Binders' Then Sum(Forecast) Else Null End)
)
/
(If Min([Sub-Category]) =  'Binders' Then Sum(Forecast) Else Null End)

Great, this would work,, in a relation world, but this is OLAP, we are unable to slice the dimension data here, with Tableau returning an unknown error for the split.

Therefore, really, this calculation would become:

Code Block
titleBasic % Diff Calc
(
    Sum(Sales)
    -
    Sum(Forecast)
)
/
Sum(Forecast)

 Which isn't what is required as this would provide an output for all the fields.

Table Calculations to the rescue

Yes, table calculations are your friends here as these use ordinal positions to provide the output:

Image Added

Being more complex, positional table calculations do generate second-level calculations needed to be processed by Tableau so you will need to consider this when building your view.

Content by Label
showLabelsfalse
max5
spacescom.atlassian.confluence.content.render.xhtml.model.resource.identifiers.SpaceResourceIdentifier@ce3c1862
showSpacefalse
sortmodified
reversetrue
typepage
cqllabel = "olap" and type = "page" and space = "TABLEAU"
labelsOLAP

...

Page Properties
hiddentrue


Related issues