5 Replies Latest reply on Jan 28, 2019 7:31 AM by Shinichiro Murakami

    Creating Columns with overlapping date restriction from input date range

    Shashank Gourav

      Hello All,

       

      I have a requirement were I need to take user input start date and end date. And then pass it to calculate various fields:-

      1. Days worked                         - Distinct count of Processed date field between input start and end dates.

      2. Processed Policies                - Sum of the Processed policies for the same period

      3. Productivity current window   - Processed Policies / Days worked

      4. Productivity Previous window - Processed Policies Previous Window / Days worked Previous Window

      5 % Change of Previous window - (Productivity current window - Productivity Previous window) / Productivity Previous window

      6. Productivity Last Year Window - Processed Policies Last Year Window / Days worked Last Year Window

      7.% Change of Last Year window - (Productivity current window - Productivity Last Year Window) / Productivity Last Year Window

       

      Where, current window      - Date between start date and end date

                Previous window      - Exact same number of days before the start date

              Last Year Window      - Dates between, one year less from start and end date

       

      I am able to calculate the first three fields with desired values, by creating start and end date parameters.

       

      But the field 4 and 6 are being populated with wrong values and the % field 5 and 7 are giving 0.

       

      It would be really helpful if you could help me I guide in the correct approach to archive this.