9 Replies Latest reply on Jun 9, 2015 2:57 AM by Suhrid Ghosh

    Dynamic Month Start and End Day

    Suhrid Ghosh

      Hello,

       

           I have a requirement where in customer would like to have a dynamic month start and end day. For eg: If i say Month Start Day = 19

      , then my current monthly calculation should be from say 19th of June to 18th of July , similarly 19th of July to 18th of August and so on...

      So if i have a monthly plot it has to show 19th of every month which will have 19th to 18th computation.

         

           I have a table with daily granularity for this...

       

           Can anyone help me on this !!!

       

      Thanks,

      Suhrid Ghosh

        • 1. Re: Dynamic Month Start and End Day
          Cyril Belmehdi

          Could you send a workbook please ?

           

          Could you also explain current and expected results.

           

          Would your client like to display the full date or only the year month ?

           

          If it's only year month a simple new date field with datadiff using a parameter should work.

           

          Thanks

          • 2. Re: Dynamic Month Start and End Day
            Suhrid Ghosh

            Hi Cyril,

             

            Thanks for looking into this ...

             

            In the above graph 01-01-2014 which has revenue from 01-01-2014 to  31-01-2014 ... Instead of that i want to display 19-01-2014 which has revenue from 19-01-2014 to 18-02-2014 ...

             

            Hope you get my requirement ...

            • 3. Re: Dynamic Month Start and End Day
              Bhushan Virulkar

              Hi,

               

              Create a parameter which will allow user to select start date dynamically:

              Parameter Name: Start date Parameter

              Definition: Data type :- Integer

              Minimum value :- 1

              Maximum value :- 29

               

              Create a calculated field.

              Field Name: Start Date

              Expression: DATEADD('day', [Start date Parameter]-1,DATETRUNC('month',[Order Date]))

              here [Order Date] is the date field to be used.

               

              Create another calculated field.

              Field Name: End Date

              Expression: DATEADD('month', 1,(DATEADD('day', [Start date Parameter]-2,DATETRUNC('month',[Order Date]))))

              here [Order Date] is the date field to be  used.

               

              I have attached the snapshot of a workbook where these fields are used.

               

              Kindly let me know if this helps.

               

               

              Thanks,

              Bhushan

              InfoCepts Technologies Pvt Ltd.

              Dynamic Dates.jpg

              • 4. Re: Dynamic Month Start and End Day
                Suhrid Ghosh

                Hi Bhushan,

                 

                The SUM(Profit) is computed from 1st Jan 2013 to 31st Jan 2013 , i want it to compute from say if the Param Value is 11 then SUM(Profit) should compute from 11th Jan 2013 to 10th Feb 2013. For the next SUM(Profit) should be computed from 11th Feb 2013 to 10th March 2013.

                 

                Thanks,

                Suhrid Ghosh

                • 5. Re: Dynamic Month Start and End Day
                  Cyril Belmehdi

                  Maybe I'm missing something but here is a proposal.

                   

                  Create a parameter to set the first day of the month

                   

                  Careful, it can't be greater than 28 because of february

                   

                  Then create a calc for a new date :

                   

                  DATEADD('day',-[First day of the month]+1,[Order Date])

                   

                  I just explain it in another post, the + 1 is normal for date calculation.

                   

                  So this calc set the 11th of january to the first the 12th the 2nd etc.

                   

                  Now do your group by, by this new date and it works

                   

                  Workbook attached

                   

                  Kind regards

                   

                  ps : if you need to start after the 28 it's easy, it's just a couple of IF to add to test the number of day in the month.

                   

                  February will be a bit more complicated since I don't know yet how to manage leap year in Tableau but again it can be done if we can get the last day of a month in Tableau

                  • 6. Re: Dynamic Month Start and End Day
                    Suhrid Ghosh

                    Hi Cyril,

                     

                    Thanks a ton for the solution. And for the leap year i need to see how we can handle it .

                     

                    Thanks,

                    Suhrid Ghosh

                    • 7. Re: Dynamic Month Start and End Day
                      Cyril Belmehdi

                      I'll remove my last messages

                       

                      It was too complex because I made some assumption which were not necessarily true.

                       

                      My assumtpion were that if the user select 29 as a month start then the 28 of february will fall the next month. I thought about it and no, it should go the next month. Same thing if the chose the 31, the 30 day month will not go next month.

                       

                      So here is a much more elegant solution for your problem:

                       

                      IF DATEPART('day',[Order Date])<[First day of the month]   Then DATEADD('day',-(DATEPART('day',[Order Date])),[Order Date])

                      ELSE DATEADD('month',1,DATETRUNC('month',[Order Date]))-1

                      END

                       

                      Tell me if it's ok for you

                      1 of 1 people found this helpful
                      • 8. Re: Dynamic Month Start and End Day
                        Cyril Belmehdi

                        Did you try this solution ?

                        • 9. Re: Dynamic Month Start and End Day
                          Suhrid Ghosh

                          Yes Cyril ... I have tried the solution and it perfectly worked !!!

                           

                          Thanks for helping me out on this. Couldn't have done with out u .

                           

                          Thanks a lot !!!

                           

                           

                          Regards,

                          Suhrid Ghosh