1 Reply Latest reply on Jul 11, 2018 5:33 PM by swaroop.gantela

    Sum Values Using Date Range as Criteria

    Toby Holland

      Hi,

       

      I'm trying to use a date range as a criteria to sum a value

       

      So in the example, I want to know the number inbound seats withing a range, for example on the 15th July there are 21 Outbound seats, i want to know how many seats there are in total between the 18th and 23rd for example

       

      The Outbound and Inbound seats are calculated fields - SUM(IIF([Out/In]="Inbound",[Seats Available],null))

       

      This is as far as i've got, but just get a table full of nulls

       

      SUM(iif([Departure Date]>= (DATEADD('day',3,[Departure Date]))

      and [Departure Date]<=(DATEADD('day',5,[Departure Date]))and [Out/In]="Inbound",

      [Seats Available],null))

       

       

      Thanks


      Toby

        • 1. Re: Sum Values Using Date Range as Criteria
          swaroop.gantela

          Toby,

           

          I'm not sure if the following two methods will be feasible for your true dataset,

          but maybe it can give ideas.

           

          One method, would be to join the datasource to itself on a calculated field of 1.

          This will create a large table of every date in conjuction with every other date.

          But this can be filtered down using the same type of calculation you described:

           

          IF [DepDate (second copy)]>=DATEADD('day',3,[Dep Date])

          AND [DepDate (second copy)]<=DATEADD('day',8,[Dep Date])

          THEN [Inbound (second copy)]

          END

           

          An alternative method is to use Lookups to look forward 3 to 8 days:

          LOOKUP(SUM([Inbound]),3)+

          LOOKUP(SUM([Inbound]),4)+

          LOOKUP(SUM([Inbound]),5)+

          LOOKUP(SUM([Inbound]),6)+

          LOOKUP(SUM([Inbound]),7)+

          LOOKUP(SUM([Inbound]),8)

           

           

          Please see workbook v10.3 attached in the Forum thread.