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

# Sum Values Using Date Range as Criteria

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

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.