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

    Time Intelligence

    Matthew Davis
    Matthew Davis Aug 15, 2012 12:51 PM

      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
          Mark Holtz
          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
            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
              Mark Holtz Aug 15, 2012 2:04 PM (in response to Matthew Davis)

              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
                Matthew Davis
                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
                  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
                    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
                      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