Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
cliffwalker
New Contributor

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

Re: Using begin and end dates to define a calendar period field

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

Re: Using begin and end dates to define a calendar period field

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

cliffwalker
New Contributor

Re: Using begin and end dates to define a calendar period field

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