1 Reply Latest reply on Aug 25, 2016 8:01 AM by Joe Oppelt

    Data Items Using Different Date Ranges

    Amy Pfarr

      I have a problem I am trying to solve in Tableau that I am
      hoping I can get some help with.  I have a report that is calculating
      percentages using different time frames for the numerators and denominators –
      the numerator is using the report period, and the denominator is using the
      report period + 1 month prior. 

      In theory, this is what it should be doing: 

      Numerator:  SUM (Net Revenue) WHERE Arrival Date
      BETWEEN MIN (Arrival Date) and MAX (Arrival Date)

      Denominator:  SUM (FTE Count) WHERE Arrival Date
      BETWEEN MIN (Arrival Date) – 1 Month AND MAX (Arrival Date)

      I am able to get both of those pieces somewhat working
      separately but as soon as I pull them back together, and am using the Date
      Range as a filter, I am losing data from one or the other. 

        • 1. Re: Data Items Using Different Date Ranges
          Joe Oppelt

          I would build a special calc.

           

          And set your date rage via a start-date parameter and an end date parameter.

           

          Your denominator calc would say

           

          if [arrival date] >= dateadd('month',-1,[start-date parameter] and

          [arrival date] <= dateadd('month',-1,[end date parameter] then [FTE Count] END

           

          You'll have a calc that will have nulls in all rows except those where the proper month-ago range is met.

           

          SUM([Denominator calc]) will be your proper denominator.

           

          Caveat:  If you filter dates with a quick filter, and the user selects a range that does not include a month ago, your SUM([denominator calc]) will be zero.

           

          Another way to address this -- and allow filtering to work -- is to make a duplicate of your data source.  In the (copy) data source create a calc like this:

           

           

           

          DATEADD('month',1,[Arrival Date])

           

          And then blend your original source with your copy source, and join [Arrival Date] from the primary and [Arrival Date Join calc] to make the connection.  All the data from the secondary source will be offset by a month, and if you just grab SUM([copy source].[FTE Count]) from the secondary source, you'll get the previous month's data connected to the current month's [Net Revenue] from the primary.  In this way you probably wouldn't even need the start-date calc and end-date calc.