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

    Time Intelligence

    Matthew Davis

      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

          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

            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

              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

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

                • 5. Re: Time Intelligence
                  Mark Holtz

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

                  • 6. Re: Time Intelligence
                    Matthew Davis

                    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

                      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