1 Reply Latest reply on Apr 11, 2018 2:29 PM by Patrick A Van Der Hyde

    How to get No. of Days by selecting From Date and To Date Parameter??

    Praveen Kumar

      Hi All,

       

      When I select parameter of current start date and current end date, for the resource Kumar should get 10 Days based on excluding StartDate and EndDate in a given table. Please anyone help me on this calculation.

       

      Example 1 :

       

      Parameter

      Current Start Date

      31-07-2017

      Current End Date

      27-08-2017

      Dataset

      Resource

      RescourceCreatedDate

      Start Date

      End Date

      Days

      Kumar

      24-02-2014

      14-08-2017

      17-09-2017

      10

      Kumar

      24-02-2014

      27-11-2017

      03-12-2017

      Kumar

      24-02-2014

      25-12-2017

      01-01-2050

       

      Example 2 :

       

      Parameter

      Current Start Date

      31-07-2017

      Current End Date

      15-12-2017

      Dataset

      Resource

      RescourceCreatedDate

      Start Date

      End Date

      Days

      Kumar

      24-02-2014

      14-08-2017

      17-09-2017

       

      70

      Kumar

      24-02-2014

      27-11-2017

      03-12-2017

      Kumar

      24-02-2014

      25-12-2017

      01-01-2050

       

      The formula which I used for calculating No. of Days is :

       

      No of working days

       

      { FIXED [Resource]: AVG(DATEDIFF('day',[Start2],[End2])

      -

      datediff('week',[Start2],[End2],'sunday')

      -

      datediff('week',[Start2],[End2],'saturday')+1)}

       

      Start 2

       

      IF [Resource Created Date]>[Current End Date]

      then

      [Current End Date]+1

      ELSE

      MAX([Resource Created Date],[Current Start Date])

      END

       

      End 2

       

      IF [startdate]<=[Current Start Date]

      then

      [Current Start Date]-1

      ELSEIF

      ISNULL([startdate])

      THEN

      [Current End Date]

      ELSE

      MIN([enddate],[Current End Date])

      END

       

      Excluded :week Ends (Saturday and Sunday)