Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using an Excel calendar to filter transactions

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

For example:

Period_StartPeriod_EndPeriod_Number
01/08/201127/08/20111
28/08/201124/09/20112
25/09/201129/10/20113

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

1 Solution

Accepted Solutions
cesaraccardi
Specialist
Specialist

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;

View solution in original post

2 Replies
cesaraccardi
Specialist
Specialist

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;

Not applicable
Author

Yes, that works. Many thanks.