3 Replies Latest reply on Mar 5, 2013 6:56 AM by Tim Hill

    Conditional date range for different measures

    Tim Hill

      Hi,

       

      I have a worksheet in my dashboard that has multiple values being displayed in rows with weeks as the column.  I have a parameter filter that allows the user to select their date range based on the below two calculated fields.  This all works great and I have no problem with them.  What's causing the issue is one of the measure values I now need to display on the row (Retention Rate below) is calculated with data from the previous week.  This calculation works correctly but because it's going back one week it's causing the first week in the date range to not show for this one measure.  If there a way to make my date range behave differently for this one measure or is there a better way to use the value from the previous week than what I have done?  Thanks in advance for your help.

       

      Selected Start Date

      CASE [Select Date Range]

      WHEN "All" THEN DATETRUNC('month',[Full Date])

      WHEN "3 months" THEN DATEADD('month',-3,TODAY())

      WHEN "6 months" THEN DATEADD('month',-6,TODAY())

      WHEN "1 year" THEN DATEADD('month',-12,TODAY())

      WHEN "2011" THEN DATETRUNC('month',#January 1, 2011#)

      WHEN "1H 2011" THEN DATETRUNC('month',#January 1, 2011#)

      WHEN "2H 2011" THEN DATETRUNC('month',#July 1, 2011#)

      WHEN "2012" THEN DATETRUNC('month',#January 1, 2012#)

      WHEN "1H 2012" THEN DATETRUNC('month',#January 1, 2012#)

      WHEN "2H 2012" THEN DATETRUNC('month',#July 1, 2012#)

      END

       

      Show Dates

      DATETRUNC('week',[Full Date])>=DATETRUNC('week',[Selected Start Date])

       

      Retention Rate

      SUM([Retained Users])/WINDOW_SUM(SUM([Unique Users]),-1,-1)

        • 1. Re: Conditional date range for different measures
          Catherine Rivier

          Hi,

          I was working on this one for a while, and was about to give up, until I came up with a simple solution!

           

          The issue is, you can't filter the dates, because once you filter, that date is "removed" from the data.  In other words, it can't look up the previous value because there is no previous value, as you found.

          However, the alternate version to a filter is to HIDE that data.  In Tableau, a Hide does exactly that - it hides, rather than filters, that value.

           

          I set up a sample workbook here.

          The first tab, "Original - with issue", basically replicates your situation, if I understand it correctly.  Similar parameter, and a Sales, and Previous Week Sales value.  Field "Show Dates" is in the Filters, to show true values only.  I'm using field "Week Date" to show each week value.

           

          The second tab, "Fix Step 1" removes that Show Dates filter, so all dates are shown.  Then I created two new fields:

          1. "Show Dates New" works exactly like "Show Dates", but gives a text output of "True" and "False" rather than the boolean so we can reference it in another calculated field.
          2. "Week Date Fixed" is key here.  It will replace the "Show Dates" filter, giving a null value for all not in the range:

                         IF [Show Dates New]='True' THEN [Week Date] End

          (Note you can use this same calculation in any other field you have in your final view you need to hide.)

           

          Then to create the third tab, "Fix Step 2 - After Hide", I just took the view in "Fix Step 1", right-clicked on one of the Null values in Week Date Fixed, and selected "Hide".  This should fix it for you!

          Catherine

          1 of 1 people found this helpful
          • 2. Re: Conditional date range for different measures
            Tim Hill

            This is a really good suggestion Catherine but it doesn't completely work for me in my situation.  My worksheet is displaying the values as line charts but you can't do this with two date fields like is required in this solution.  I verified that I can get all of the data displaying correctly with your method which is good and it seems very close but it charts it with individual points for each week instead of the lines that I require.  I've attached an example that has a dashboard with two similar worksheets (one 30 day values and one 60 day values) so you can see a side by side comparison of what I have now (60 day) and what I get with these changes (30 day).  I'll continue to try and correct this but so far I can not figure out how to implement this fix, which does do what I want in terms of data, and still keep the line charts.

            • 3. Re: Conditional date range for different measures
              Tim Hill

              I kept thinking along the lines of hiding the data I didn't want in the current view of the charts instead of filtering it and remember something similar I had done for a running sum in another chart that needed to keep adding but not display outside the date range.  The fix was to create a week filter with the formula below to use in the Show Dates and Show Dates End calculated fields so that they work the same for the comparisons as before but now they don't actually filter out the actual data.  This is working great and fixes the problem.

               

              lookup(attr([Week]),0)