2 Replies Latest reply on Jan 5, 2018 5:02 PM by swaroop.gantela

    Sum by Day When Given a Range

    David Data

      I have attached the workbook that I'm working on. I have 4 jobs that span over multiple days. But I want a report that shows me the total estimated revenue by day. I have my logic 90% of the way there but I'm not sure how to execute it. I want to take the total for the job, and divide it by the number of the days in the range (I have a start date and an end date) to get a 'daily rate'. Then I want the day, if in that range, to add that daily rate in so I can see the total I am estimated to make by day.

       

      What I want the output to be is basically this:

       

        

      DateRevenue
      1/1/2018 $  185.73
      1/2/2018 $  185.73
      1/3/2018 $  185.73
      1/4/2018 $  185.73
      1/5/2018 $  185.73
      1/6/2018 $  185.73
      1/7/2018 $  185.73
      1/8/2018 $  185.73
      1/9/2018 $  185.73
      1/10/2018 $  185.73
      1/11/2018 $  185.73
      1/12/2018 $  185.73
      1/13/2018 $  185.73
      1/14/2018 $  185.73
      1/15/2018 $  185.73
      1/16/2018 $  185.73
      1/17/2018 $  185.73
      1/18/2018 $  185.73
      1/19/2018 $  185.73
      1/20/2018 $  185.73
      1/21/2018 $  185.73
      1/22/2018 $  185.73
      1/23/2018 $  185.73
      1/24/2018 $  185.73
      1/25/2018 $  185.73
      1/26/2018 $  185.73
      1/27/2018 $  185.73
      1/28/2018 $  185.73
      1/29/2018 $  185.73
      1/30/2018 $  185.73
      1/31/2018 $  185.73
      2/1/2018 $  185.73
      2/2/2018 $  185.73
      2/3/2018 $  185.73
      2/4/2018 $  185.73
      2/5/2018 $  185.73
      2/6/2018 $  185.73
      2/7/2018 $  185.73
      2/8/2018 $  185.73
      2/9/2018 $  185.73
      2/10/2018 $  185.73
      2/11/2018 $  185.73
      2/12/2018 $  185.73
      2/13/2018 $  185.73
      2/14/2018 $  277.29
      2/15/2018 $  277.29
      2/16/2018 $  277.29
      2/17/2018 $  277.29
      2/18/2018 $  277.29
      2/19/2018 $  277.29
      2/20/2018 $  277.29
      2/21/2018 $  277.29
      2/22/2018 $  277.29
      2/23/2018 $  277.29
      2/24/2018 $  277.29
      2/25/2018 $  277.29
      2/26/2018 $  277.29
      2/27/2018 $  277.29
      2/28/2018 $  277.29
      3/1/2018 $  277.29
      3/2/2018 $  277.29
      3/3/2018 $  277.29
      3/4/2018 $  277.29
      3/5/2018 $  277.29
      3/6/2018 $  277.29
      3/7/2018 $  277.29
      3/8/2018 $  277.29
      3/9/2018 $  277.29
      3/10/2018 $  228.28
      3/11/2018 $  228.28
      3/12/2018 $  228.28
      3/13/2018 $  228.28
      3/14/2018 $  228.28
      3/15/2018 $  228.28
      3/16/2018 $  228.28
      3/17/2018 $  228.28
      3/18/2018 $  228.28
      3/19/2018 $  228.28
      3/20/2018 $  228.28
      3/21/2018 $  228.28
      3/22/2018 $  228.28
      3/23/2018 $  228.28
      3/24/2018 $  228.28
      3/25/2018 $  228.28
      3/26/2018 $  228.28
      3/27/2018 $  228.28
      3/28/2018 $  228.28
      3/29/2018 $  228.28
      3/30/2018 $  228.28
      3/31/2018 $  228.28
      4/1/2018 $  228.28
      4/2/2018 $  228.28
      4/3/2018 $  228.28
      4/4/2018 $  228.28
      4/5/2018 $  228.28
      4/6/2018 $  228.28
      4/7/2018 $  228.28
      4/8/2018 $  228.28
      4/9/2018 $  228.28
      4/10/2018 $  228.28
      4/11/2018 $  228.28
      4/12/2018 $  228.28
      4/13/2018 $  228.28
      4/14/2018 $  228.28
      4/15/2018 $  228.28
      4/16/2018 $  228.28
      4/17/2018 $  228.28
      4/18/2018 $  228.28
      4/19/2018 $  228.28
      4/20/2018 $  228.28
      4/21/2018 $  228.28
      4/22/2018 $  228.28
      4/23/2018 $  228.28
      4/24/2018 $  228.28
      4/25/2018 $  228.28
      4/26/2018 $  228.28
      4/27/2018 $  228.28
      4/28/2018 $  228.28
      4/29/2018 $  228.28
      4/30/2018 $  228.28
      5/1/2018 $  228.28
      5/2/2018 $  228.28
      5/3/2018 $  228.28
      5/4/2018 $  228.28
      5/5/2018 $  228.28
      5/6/2018 $  228.28
      5/7/2018 $  228.28
      5/8/2018 $  228.28
      5/9/2018 $  228.28
      5/10/2018 $  228.28
      5/11/2018 $  228.28
      5/12/2018 $  228.28
      5/13/2018 $  228.28
      5/14/2018 $  228.28
      5/15/2018 $  228.28
      5/16/2018 $  228.28
      5/17/2018 $  228.28
      5/18/2018 $  228.28
      5/19/2018 $  228.28
      5/20/2018 $  228.28
      5/21/2018 $  228.28
      5/22/2018 $  228.28
      5/23/2018 $  228.28
      5/24/2018 $  228.28
      5/25/2018 $  228.28
      5/26/2018 $  228.28
      5/27/2018 $  228.28
      5/28/2018 $  228.28
      5/29/2018 $  228.28
      5/30/2018 $  228.28
      5/31/2018 $  228.28
      6/1/2018 $  228.28
      6/2/2018 $  228.28
      6/3/2018 $  228.28
      6/4/2018 $  228.28
      6/5/2018 $  228.28
      6/6/2018 $  228.28
      6/7/2018 $  228.28
      6/8/2018 $  228.28
      6/9/2018 $  228.28
      6/10/2018 $  228.28
      6/11/2018 $  136.73
      6/12/2018 $  136.73
      6/13/2018 $  136.73
      6/14/2018 $  136.73
      6/15/2018 $  136.73
      6/16/2018 $  136.73
      6/17/2018 $  136.73
      6/18/2018 $  136.73
      6/19/2018 $  136.73
      6/20/2018 $  136.73
      6/21/2018 $  136.73
      6/22/2018 $  136.73
      6/23/2018 $  136.73
      6/24/2018 $  136.73
      6/25/2018 $  136.73
      6/26/2018 $  136.73
      6/27/2018 $  136.73
      6/28/2018 $  136.73
      6/29/2018 $  136.73
      6/30/2018 $  136.73

      Those are accurate numbers from a manual process I did in excel just to validate my data.

       

      I built all my formulas but the one that's giving me an error is:

       

      IIF([Everyday].[Everyday]>=[Projected Start Date] and [Everyday].[Everyday]<=[Projected End Date],[Daily Revenue],0)

       

      It is because [Everyday].[Everyday] is being forced as an Attr. If I could fix this formula I think my whole report will work the way I'd like to see it.