1 Reply Latest reply on Feb 2, 2018 6:06 PM by Manuel Velasquez

    Trying to implement relative date filter period to previous period comparisons

    Mark McHenry

      I have a worksheet that uses an exact date dimension (create timestamp) for a relative date filter. I would like to be able to take whatever relative period the user selects (previous day, last 7 days, last week, last 2 quarters, etc) and also display values from that period one interval in the past. Here's an example of what I would like to accomplish:

       

      I publish a sheet that displays the number of widgets created. The user can choose a relative date period to see how many widgets were created in their chosen period.

       

      The user selects the period 'last 7 days' and the number of widgets created in the last 7 days is displayed as a bar graph. Additionally, there is a mark on the bar graph that shows the number of widgets created 7 to 14 days ago (the same relative period selected by the user, but one interval back). If the user selects the period 'last 2 days' the graph shows a bar graph of the widgets created in the last 2 days and an additional mark showing the number of widgets created 2 to 4 days ago (a second 2 day period, one interval in the past).

       

      I haven't been able to find a way to implement this. Any suggestions would be greatly appreciated. If the question is unclear or additional information is needed please let me know.

        • 1. Re: Trying to implement relative date filter period to previous period comparisons
          Manuel Velasquez

          Hi Mark

           

          Let me show one way to use a parameter to create the view you are looking.

          For this example I'm using Sample-Superstore Dataset I added more rows with 2018 data so we can have TODAY() values

           

          The parameter looks like this

          1.PNG

           

           

          Now every time you select an element in this parameter you are specifying 2 periods a "Period to Show" and  a "Period to Compare"

           

           

           

          Here are my calculations to show quantities sold for each period selected

          2.PNG

           

          3.PNG

          NOTE: You might want to play in the Quarter and Year part you could use days if that makes more sense to you, this is for simplifying 

           

           

          Doble checking

           

          4.png

          NOTE: TODAY() is an inclusive date.

           

          The View

          Bar represents the period to show(including today) Line, represent period to compare(Same period back)

           

           

          5.PNG

           

          Attached .twbx file for reference ( Ver 10.5)

           

          Let me know if this helps!

           

          Manuel Velasquez