7 Replies Latest reply on Aug 16, 2012 9:06 AM by Mark Holtz

# Time Intelligence

I am working with simple bar charts that look at expenses for my top vendors. I want my user to be able to filter the data based on time. I was hoping to add a sheet or quick filter to the dashboard that will contain text (LY, YTD, MTD, QTD) and when selected the expenses amount will be filtered to the specified time dimension.  In powerpivot i was able to accomplish this by creating a calculated field using a dax formula

=IF(COUNTROWS(VALUES(Table2[Calc]))=1,

IF(VALUES(Table2[Calc]) = "YTD", WWOps[Sum of MeasuresExpenses Amount Wwops](DatesYTD(Calendar[Posting DateCalendarDate])),

IF(VALUES(Table2[Calc]) = "QTD", WWOps[Sum of MeasuresExpenses Amount Wwops](DatesQTD(Calendar[Posting DateCalendarDate])),

IF(VALUES(Table2[Calc]) = "MTD", WWOps[Sum of MeasuresExpenses Amount Wwops](DatesMTD(Calendar[Posting DateCalendarDate])),

IF(VALUES(Table2[Calc]) = "LY", WWOps[Sum of MeasuresExpenses Amount Wwops],(Year(PreviousYear(Calendar[Posting DateCalendarDate])))

)))))

I was hoping to do something similar.

• ###### 1. Re: Time Intelligence

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

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

Would 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

1 of 1 people found this helpful
• ###### 4. Re: Time Intelligence

That actually could work in theory however I am gettig a syntax error on the Else

• ###### 5. Re: Time Intelligence

Can you share the detail of the error or post your workbook?

• ###### 6. Re: Time Intelligence

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

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