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?