1 2 Previous Next 20 Replies Latest reply on Dec 18, 2015 6:43 AM by Norbert Maijoor

    Remaining days calculation  (Start date and End date)

    siva goberu

      Hi,

       

       

      how to calculate remaining days in a month .

       

      I have two parameter controls start date and end date .

      when the user select a range of dates(Start date-- 11/5/2015  End date ---11/10/2012) from start date and end date.

      how can i calculate remaining days (11/1/2015 to 11/4/2015   and 11/11/2015 to 11/30/2015)

       

      Regards,

      Siva G

        • 1. Re: Remaining days calculation  (Start date and End date)
          Shanaka Gunaratna

          Hi Siva

          Create two parameters FromDate and ToDate

          Then create a calculation field and add following

           

          // Count from Fist day of the month to FromDate - 1

          DATEDIFF ('day', DATETRUNC('month',DATEADD('month', -1, DATETRUNC('month',DATEADD('month',1,[ToDate])) )), [FromDate])

          +

          // Count from To Date + 1 To End of the Month

          DATEDIFF ('day', dateadd('day', 1, [ToDate]), DATETRUNC('month',DATEADD('month',1,[ToDate]))-1)

          1 of 1 people found this helpful
          • 2. Re: Remaining days calculation  (Start date and End date)
            Norbert Maijoor

            Hi Siva!

             

            Find my approach in attached workbook in version 9.0. A "'pre-requisit" is that all calendar-days schould be available in applicable database.

             

            days untill.png

            • 3. Re: Remaining days calculation  (Start date and End date)
              siva goberu

              Hi  shanaka.,

              Thanks for the quick response .

               

              The formula which u have sent is giving the total days between (From date  to  To date) Parameter controls

               

              Example:

              From date(1/10/2015)         To Date( 1/15/2015)

              5 days

               

              i need the remaining days for the month a part from the user selected dates from (From date  to  To date) Parameter controls

              25 days

               

              Can u please try to send the required result

               

              Regards,

              Siva G

              • 4. Re: Remaining days calculation  (Start date and End date)
                Shanaka Gunaratna

                Hi Siva

                I think my solution provide the result which your after. Did you checked my packaged work book (attached with original reply)

                 

                Shanaka

                • 5. Re: Remaining days calculation  (Start date and End date)
                  siva goberu

                  Hi Shanaka,

                  Remaindays in month.png

                  i checked your worksheet but its not giving the result what i am looking for.

                  Please check the mail which i have earlier sent u.

                   

                  i need remaining days of the month

                   

                   

                   

                  Regards,

                  Siva G

                  • 6. Re: Remaining days calculation  (Start date and End date)
                    Tableau kumar

                    Hi,

                     

                    if you want to see remaining days in the month, use the following syntax.

                     

                    datediff('day', [Order Date], dateadd('day', -1,  datetrunc( 'month', dateadd('month', 1, [Order Date]))))

                     

                    I did not check syntax whether it is having enough braces.

                     

                    The following link may help you. It is not exact solution for your situation but may useful.

                    Tableau BI: How to get the Last Day of the Given Date Data.

                     

                     

                    Best Regards

                    Kumar

                    • 7. Re: Remaining days calculation  (Start date and End date)
                      siva goberu

                      Hi Kumar,

                       

                      find the remaining days depending upon the user selection of dates from Parameter controls(FROM DATE(Start Dates) , TO DATE (End Dates))

                       

                       

                      I calculated days count of days between FROM DATE to TO DATE by using formula in the Calculation field named (Days  FROM DATE to TODATE)

                       

                       

                      DATEDIFF ('day', DATETRUNC('month',DATEADD('month', -1, DATETRUNC('month',DATEADD('month',1,[Start Dates])) )), [End Dates])

                      +

                      DATEDIFF ('day', dateadd('day', 1, [Start Dates]), DATETRUNC('month',DATEADD('month',1,[End Dates]))-1)

                       

                       

                      I calculated Total no of days in a month by using formula in the calculation field named (Total no of days in amonth)

                       

                       

                      DATEDIFF('day',min([Dates]),min(dateadd('month',1,[Dates])) )

                       

                       

                      Now i created new calculatin field named remainning days

                       

                      (Total no of days in amonth - Days  FROM DATE to TODATE)

                       

                      But the calculation field remaining days not giving the exact result .

                       

                      Can u please help me.

                       

                       

                       

                      Regards,

                      Siva G

                      • 8. Re: Remaining days calculation  (Start date and End date)
                        siva goberu

                        Hi shanaka,

                         

                        find the remaining days depending upon the user selection of dates from Parameter controls(FROM DATE(Start Dates) , TO DATE (End Dates))

                         

                         

                        I calculated days count of days between FROM DATE to TO DATE by using formula in the Calculation field named (Days  FROM DATE to TODATE)

                         

                         

                        DATEDIFF ('day', DATETRUNC('month',DATEADD('month', -1, DATETRUNC('month',DATEADD('month',1,[Start Dates])) )), [End Dates])

                        +

                        DATEDIFF ('day', dateadd('day', 1, [Start Dates]), DATETRUNC('month',DATEADD('month',1,[End Dates]))-1)

                         

                         

                        I calculated Total no of days in a month by using formula in the calculation field named (Total no of days in amonth)

                         

                         

                        DATEDIFF('day',min([Dates]),min(dateadd('month',1,[Dates])) )

                         

                         

                        Now i created new calculatin field named remainning days

                         

                        (Total no of days in amonth - Days  FROM DATE to TODATE)

                         

                         

                        But the calculation field remaining days not giving the exact result .

                         

                         

                        Can u please help me.

                        • 9. Re: Remaining days calculation  (Start date and End date)
                          Tableau kumar

                          I have created calculated field using the DATE FIELD directly, and you can replace it by Parameter, This Parameter makes User can select the Date.

                           

                          1) If you want to see remaining days in the month, use the following syntax.

                           

                          Name: No. of days left in the Month - Selected Date

                          Syntax: datediff('day', [Date Parameter], dateadd('day', -1,  datetrunc( 'month', dateadd('month', 1,[Date Parameter]))))

                           

                           

                          2) If you want to know total No. of days in the month with respect to selected date, use the following syntax.

                          Name: Total no. of Days in the Given Month

                          Syntax: datepart('day', [Date Parameter]) + [ No. of days left in the Month - Selected Date]) -1

                          • 10. Re: Remaining days calculation  (Start date and End date)
                            siva goberu

                            Hi Norbert,

                             

                            Example:

                            I created two parameter controls FROMDATE and TODATE

                            From date  (Parameter control)

                            To date (Parameter control)

                            i need the remaining days of the month when the user selects:

                            From date(1/10/2015)         To Date( 1/15/2015)  ------ 5 days

                             

                            i need the remaining days for the month a part from the user selected dates from (From date  to  To date) Parameter controls

                            25 days

                             

                            Can u please try to send the required result

                            Regards,

                            Siva G

                            • 11. Re: Remaining days calculation  (Start date and End date)
                              Norbert Maijoor

                              Siva!

                               

                              I will check and update you a.s.a.p.

                              Have a nice day!

                              • 12. Re: Remaining days calculation  (Start date and End date)
                                Norbert Maijoor

                                Good morning Siva!

                                 

                                The following. I hope I can explain my attemps clearly.

                                 

                                I defined 3 indicators

                                 

                                a. FROMDATE untill End of Month

                                b. TODATE untill End of Month

                                c. FROMDATE untill TODATE

                                 

                                The FROMDATE & TODATE are based on a Parameter.

                                "End of Month" is defined based on LOD expression {fixed [Order Date (Month / Year)]: max([Order Date])}

                                 

                                To be honest with you I do not understand your last sentence:

                                 

                                i need the remaining days for the month a part from the user selected dates from (From date  to  To date) Parameter controls

                                25 days

                                 

                                 

                                Could you elaborate once again on what you are aiming for?

                                 

                                Siva.png

                                 

                                Look forward hearing from you.Have a nice day

                                • 13. Re: Remaining days calculation  (Start date and End date)
                                  siva goberu

                                  Hi Norbert,

                                  Please check the image for clear understanding.

                                  I have two parameters -1. Start dates 2. End dates

                                   

                                  I created 1 st calculation field  named (Days between start date and End date) to count days  selected by the user from parameter controls--( Start dates and  End dates -----------11/1/2015 and 11/17/2015)

                                  Formula:

                                  DATEDIFF ('day', DATETRUNC('month',DATEADD('month', -1, DATETRUNC('month',DATEADD('month',1,[Start Dates])) )), [End Dates])

                                  +

                                  DATEDIFF ('day', dateadd('day', 1, [Start Dates]), DATETRUNC('month',DATEADD('month',1,[End Dates]))-1)

                                   

                                   

                                  I created 2nd Calculation field  named (Total no of days in a month)

                                  Formula:

                                  DATEDIFF('day',min([Dates]),min(dateadd('month',1,[Dates])) )

                                   

                                   

                                  Now i created 3 rd calculation field named (Remain days in a month)

                                  Formula:

                                  (Total no of days in a month) - (Days between start date and End date)

                                   

                                  But the calculation field remaining days not giving the exact result .

                                   

                                  The result i shown you in Red box in the image

                                   

                                  Can u please help me.

                                   

                                   

                                  Remaining days.png

                                  • 14. Re: Remaining days calculation  (Start date and End date)
                                    Norbert Maijoor

                                    Siva!

                                     

                                    I think I have got the indicators but it is far from perfect;)

                                     

                                    a. Days in month is based on the FROMDATE

                                    b. The calculation is only valid when FROMDATE & TODATE is selected in one month.

                                    c.The TODATE should be "filtered" (Only relevant Values") based on FROMDATE selection.

                                     

                                    siva3.png

                                     

                                    Let me know your feedback

                                    1 of 1 people found this helpful
                                    1 2 Previous Next