Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using begin and end dates to define a calendar period field

Hi, I am new to Qlik Sense, so I suspect there is an easy way to do this.

I have two tables.

ApInv2 - which shows the accounts payable invoice detail lines

And a second table called AcctPer, which shows the irregular sized accounting calendar periods (cal-period) that are used.  The Start-date and End-date fields define the size of each period and are in Julian format (which Qlik is able to interpret and convert to a regular date).  The calendar periods are in various sizes (4-5 weeks each) and sometimes span two different months (ie July 1 through Aug 4th).

I would like to be able to create a bar chart using cal-period and have the Inv-date grouped in the appropriate cal-period to give me the AP invoice Amt for each cal-period.

Not sure how to go about this.  I am assuming I will be using the Start-date and End-date to determine which Cal-period each Inv-date belongs to.  Will I be using some form of Set Analysis to create a new calculated field on the ApInv2 table?

1 Solution

Accepted Solutions
Nicole-Smith

You'll want to use IntervalMatch() in your load script so the tables are linked properly: https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalM...

The code you will need will look something like this:

ApInv2:

LOAD [Inv-doc-id],

    [Inv-date],

    Vendor,

    Item,

    [Line-no],

    [Unit-price],

    [Qty]

FROM YourSource;

AcctPer:

LOAD [Cal-period],

    [Start-date],

    [End-date]

FROM YourSource;

INNER JOIN (AcctPer)

IntervalMatch ([Inv-date])

LOAD [Start-date],

    [End-date]

Resident AcctPer;

Then you'll be able to use [Cal-period] as the dimension on your bar chart because it will be linked properly on the back-end.

View solution in original post

2 Replies
Nicole-Smith

You'll want to use IntervalMatch() in your load script so the tables are linked properly: https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalM...

The code you will need will look something like this:

ApInv2:

LOAD [Inv-doc-id],

    [Inv-date],

    Vendor,

    Item,

    [Line-no],

    [Unit-price],

    [Qty]

FROM YourSource;

AcctPer:

LOAD [Cal-period],

    [Start-date],

    [End-date]

FROM YourSource;

INNER JOIN (AcctPer)

IntervalMatch ([Inv-date])

LOAD [Start-date],

    [End-date]

Resident AcctPer;

Then you'll be able to use [Cal-period] as the dimension on your bar chart because it will be linked properly on the back-end.

Anonymous
Not applicable
Author

Thank you Nicole, that worked!  I'm glad I asked!