0 Replies Latest reply on Feb 1, 2013 2:22 AM by Grzegorz Mikulski

    Combining dimensions on a CUBE

    Grzegorz Mikulski

      Hi!

       

      I have connection to a cube that holds all data that our entities report. I am having some problems with getting the right data - so maybe someone can give me a hint.

       

      Let's assume we have following data:

       

      Data Entry Period_MonthPeriod_MonthFlowTypeAmount
      2012.102012.10M99 - Monthly (MTD)Actual100
      2012.102012.10F99 - ClosingActual1100
      2012.102012.12Q99 -  Quarterly (QTD)Forecast300
      2012.102012.12F99 - ClosingForecast1400
      2012.112012.11M99 - Monthly (MTD)Actual120
      2012.112012.11F99 - ClosingActual1220
      2012.122012.12M99 - Monthly (MTD)Actual140
      2012.122012.12Q99 -  Quarterly (QTD)Actual360
      2012.122012.12F99 - ClosingActual1360
      2013.012013.03Q99 -  Quarterly (QTD)Forecast410

       

      Companies report on monthly basis (field Data Entry Period), but they supply both Actual data, and Forecasts.

       

      On top of that we have flows - whether it is monthly, quarterly, or Year-to-date figure. So for example :

      - in line 1 we have actual figure of 2012.10 (entered in 2012.10)

      - in line 3 we have a forecast of quarterly figure in 2012.12 (forecast made in 2012.10).

       

      Data presented in Tableau looks like this:

       

      What I want to see is this (only needed fields are displayed, like I only need F99 year-to-date value to be displayed in 2012.12, not in 2012.10 or 2012.11):

       

      I cannot just right click on dimensions and hide them on selected months. If i would hide F99 in 2012.10, that it would not be shown in 2012.12 as well. So my idea was to use combined field:

       

      [Data Entry Period_Month] + " " +[Period_Month] + " " +[Flow] + " " +[Type]

       

      This works pretty well on a flat file and idea is ok, if data source is Excel.

       

      But how can I apply this on a cube? I cannot create a calculated field, only a calculated member. And when I try to use a calculated member, I end up with Tableau trying to calculate it with no result.

       

      Does anyone have an idea how to use a combined field on a cube? Or maybe there is another way around this that I do not know of?

       

      I apprecieate all suggestions.

      Best regards,

      Grzegorz