-
1. Re: Time Intelligence
Mark Holtz Aug 15, 2012 12:56 PM (in response to Matthew Davis)You should be able to do this with a Parameter and a calculated field for your measures.
Assuming you have the logic for your various time expressions, you can create a Parameter in Tableau called "ChoosePeriod"
and make it a string list of your options.
Then, make a calculated field for "Selected Measure" or whatever you'd like to call it as:
IF [ChoosePeriod] = 'LY' THEN [LastYearMeasure]
ELSEIF [ChoosePeriod] = 'YTD' THEN [YearToDateMeasure]
ELSEIF [ChoosePeriod] = 'MTD' THEN [MonthToDateMeasure]
ELSEIF [ChoosePeriod] = 'QTD' THEN [QuarterToDateMeasure]
ELSE 0
END
Another thing I like to do when I create parameter-based measures is to insert the Parameter into the view title, so it'll actually say "YTD" or "LY" somewhere on the view.
Hope that helps.
-
2. Re: Time Intelligence
Matthew Davis Aug 15, 2012 1:14 PM (in response to Mark Holtz)Do you have any suggestions on creating the logic for the time expressions within tableau, I haven't integrated the time intelligence into my SSAS cube yet.
-
3. Re: Time Intelligence
Mark Holtz Aug 15, 2012 2:04 PM (in response to Matthew Davis)1 of 1 people found this helpfulWould these work?
LY Measure:
IF YEAR([DateDimension]) = YEAR(today)-1
THEN [Measure]
ELSE 0
END
YTD Measure:
IF YEAR([DateDimension]) = YEAR(today) AND [DateDimension] <= today()
THEN [Measure]
ELSE 0
END
MTD Measure:
IF YEAR([DateDimension]) = YEAR(today()) AND MONTH([DateDimension]) = MONTH(today()) AND [DateDimension] <= today()
THEN [Measure]
ELSE 0
END
QTD Measure:
IF YEAR([DateDimension]) = YEAR(today()) AND DATEPART('quarter',[DateDimension]) = DATEPART('quarter',today()) AND [DateDimension] <= today()
THEN [Measure]
ELSE 0
END
-
4. Re: Time Intelligence
Matthew Davis Aug 15, 2012 2:16 PM (in response to Mark Holtz)That actually could work in theory however I am gettig a syntax error on the Else
-
5. Re: Time Intelligence
Mark Holtz Aug 16, 2012 5:22 AM (in response to Matthew Davis)Can you share the detail of the error or post your workbook?
-
6. Re: Time Intelligence
Matthew Davis Aug 16, 2012 8:20 AM (in response to Mark Holtz)I am actually running into a slightly different problem at the moment.
Since my data is coming from a cube the date dimension I am working with is a hierarchy and looks like:
[Posting Date]
[Year]
[Quarter Name]
[Month Name]
[Date]
I need to create a calculated member for [posting date] so it can be used in a calculated field.
In SQL Server I was able to query all of the dates in the dimension using the MDX Expression
[Posting Date].[Calendar].[Date]
But when I try and use anything similar in tableau it returns a single value that is 12/30/1899 which is also strange because the earliest date in the dimension is 01/01/1900
-
7. Re: Time Intelligence
Mark Holtz Aug 16, 2012 9:06 AM (in response to Matthew Davis)Sorry--I think your problem is a bit beyond my experience...
I haven't worked much with cubes as sources in Tableau.
Is there a way to summon the [Date] out of the [Posting Date] hierarchy?
To be clear, I'm talking about making these measures within Tableau--I don't know if it treats data from a cube different than from a SQL source or Excel.
Something like:
LY Measure:
IF YEAR(date([DateDimension])) = YEAR(today)-1
THEN [Measure]
ELSE 0
END