Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
Thank you Nicole, that worked! I'm glad I asked!