topic Re: Using begin and end dates to define a calendar period field in App Development

Using begin and end dates to define a calendar period field

Anonymous — Thu, 26 Jul 2018 21:11:08 GMT

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?

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

Nicole-Smith — Thu, 26 Jul 2018 21:40:15 GMT

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/IntervalMatch.htm

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.

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

Anonymous — Fri, 27 Jul 2018 20:48:18 GMT

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