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_Month||Period_Month||Flow||Type||Amount|
|2012.10||2012.10||M99 - Monthly (MTD)||Actual||100|
|2012.10||2012.10||F99 - Closing||Actual||1100|
|2012.10||2012.12||Q99 - Quarterly (QTD)||Forecast||300|
|2012.10||2012.12||F99 - Closing||Forecast||1400|
|2012.11||2012.11||M99 - Monthly (MTD)||Actual||120|
|2012.11||2012.11||F99 - Closing||Actual||1220|
|2012.12||2012.12||M99 - Monthly (MTD)||Actual||140|
|2012.12||2012.12||Q99 - Quarterly (QTD)||Actual||360|
|2012.12||2012.12||F99 - Closing||Actual||1360|
|2013.01||2013.03||Q99 - Quarterly (QTD)||Forecast||410|
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.