Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Valued Contributor

Re: Using an Excel calendar to filter transactions

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;

2 Replies
cesaraccardi
Valued Contributor

Re: Using an Excel calendar to filter transactions

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

Re: Using an Excel calendar to filter transactions

Yes, that works. Many thanks.