Using an Excel calendar to filter transactions

[#1]

I have a spreadsheet financial calendar with fields Period_Start, Period_End, Period_Number.

For example:

Period_Start	Period_End	Period_Number
01/08/2011	27/08/2011	1
28/08/2011	24/09/2011	2
25/09/2011	29/10/2011	3

How do I use the financial period (Period_Number) to filter transactions, say a list of sales orders for a table called Order_Detail, with the transaction date being Order_Date? By selecting the period number, the QV table should list sales orders for that financial period.

Many thanks for any help with this.

regards

Bruce

[cesaraccardi · Accepted solution]

Hi,

I think you can use INTERVALMATCH for this, take a look at this example:

TMP_Periods:
LOAD * INLINE [
    PeriodStart, PeriodEnd, PeriodNumber
    01/08/2011, 27/08/2011, 1
    28/08/2011, 24/09/2011, 2
];
Data:
LOAD * INLINE [
    Date, Value
    15/08/2011, 5
    25/08/2011, 10
    13/09/2011, 15
];
Periods:
IntervalMatch(Date) LOAD PeriodStart, PeriodEnd RESIDENT TMP_Periods;
LEFT JOIN
LOAD * RESIDENT TMP_Periods;
DROP TABLE TMP_Periods;

[#3]

Yes, that works. Many thanks.