5 Replies Latest reply on Aug 19, 2016 9:44 AM by Joe Oppelt

    Urgent : data for selected date range up to n months back

    Suresh Bandela

      Hi Tableau community !

       

      I am a newbie to tableau and at the moment trying to solve a problem.

       

      Briefly the problem is :

      1. I want to display data for selected date range up to n months back.

      A scenario, the values should be displayed for the same date range for months ( N periods back ) i.e... if i select 01/08/16 - 09/08/16 and n periods back 2 then i should have the previous period values for 01/07/16- 09/07/16 and 01/06/16 -09/06/16.

       

      At the moment the correct values are displayed only if n period back = 1 and for the rest of values it gives back the total month values. Couldn't figure out whats going wrong here

       

      Am using this Logic :

      IF [N Periods Back ] = 1 and

      [Date] >= dateadd([Comparison Period],-[N Periods Back ],[Start Date]) and [Date]<= dateadd([Comparison Period],-([N Periods Back ]),[End Date]) then [Channel costs]

      ELSEIF [N Periods Back ] = 2 and

      [Date] >= dateadd([Comparison Period],-[N Periods Back ],[Start Date]) and [Date]<= dateadd([Comparison Period],-([N Periods Back ]-1),[End Date]) then [Channel costs]

      ELSEIF  [N Periods Back ] = 3 and

      [Date] >= dateadd([Comparison Period],-[N Periods Back ],[Start Date]) and [Date]<= dateadd([Comparison Period],-([N Periods Back ]-2),[End Date]) then [Channel costs]

      END

       

      Could you please help me out with this ?

       

      Thanks !

        • 1. Re: Urgent : data for selected date range up to n months back
          Luciano Vasconcelos

          Good morning.

          I started but i got stuck.

          Can you help on this John Sobczak, Shinichiro Murakami, Joe Oppelt?

           

          Regards.

          • 2. Re: Urgent : data for selected date range up to n months back
            Joe Oppelt

            I'm looking at this, and I need some more clarity.

             

            You have a Start Date and End Date parameter.  From your description it looks like you expect the user to select some portion of a month.  (First of the month through 9th of the month, for example, as the sample workbook is initially uploaded here.)

             

            And then based on [N Periods Back], you want to show N-many historical months, summing up only data from the first through the 9th of those months.

             

            My supposition is that you will be showing sums of measures within the stretch of days, on a month by month basis.

             

            But your Start and End parameters will let me cross a month boundary.  Or multiple months.  If I select June 1 through August 9, and then I select [N Periods Back] to be 3, what do you expect to happen?

             

            If your user selects May 20 through May 31, and then selects N = 4, what would you want the logic to do for February, given that February doesn't have 31 days?

             

            These are some initial questions I have.

            • 4. Re: Urgent : data for selected date range up to n months back
              Suresh Bandela

              Luciano Vasconcelos Thanks a ton for your help and support !!

               

              Joe Oppelt Thank you very much for your reply Joe.

               

              Your understanding of requirement is spot on , i want to display the measures based on a portion of month with a flexibility for the users to select as many months backwards as they would like.

              First Scenario you have raised :

              As the scenario you mentioned that start date and end date parameters cross the boundary of month, you are right , i wasn't thinking of this Scenario's and its now clear to me that its a flaw.

               

              Second Scenario: 

              Also, the  Second scenario about going 4 periods back from may to february, my thinking was, it should display only until 28th of February but as you have pointed correctly, this wouldn't work either.

               

              I am a newbie to Taebleau and  tried to come up with the logic to get the required result following some examples i have found. .

               

              My only objective was to get  i want to display the measures based on a portion of month with a flexibility for the users to select as many months backwards as they would like.  ( is it possible to achieve when  may be the user could select just one date parameter than two ? )

               

              Could you please help me out with any alternative approach possible in order to get the result ? 

               

              Much appreciate your help !!

               

               

              • 5. Re: Urgent : data for selected date range up to n months back
                Joe Oppelt

                See attached.

                 

                In Sheet 4 I built a [Get Days] filter.  This will limit the rows to the day range the user selects.

                 

                In Sheet 5 I built two calcs and displayed them for testing purposes.  I built a start date calc and an end date calc.  they determine the first day of the beginning of the month range based on the user's selections for start date, and the last day of the end month the user selects.

                 

                In Sheet 6 I created a calc to get the full date range and put that on the filter shelf with the [Get Days] filter.

                 

                [Get Months] grabs the full range of rows from the first day of the beginning month to the last day of the end month, and [Get Days] grabs only the smaller range of days the user selects.