This QlikView shows how basic Set Analysis syntax can be used to deliver a Prior Period Comparison. Rather than taking the approach of flagging r...
This QlikView shows how basic Set Analysis syntax can be used to deliver a Prior Period Comparison.
Rather than taking the approach of flagging rows as being in certain periods at load time this example shows the comparisons based on selections. It is an approach I have used many times on client site and tends to offer the users what they require.
The QlikView was originally written as a response to the following QlikCommunity thread: http://community.qlik.com/message/102230
If you want to copy and paste the table from this example into another document you can set all the variables by adding this code to your load script:
let vMaxDate = '=max(Date)';
let vMaxDay = '=day(max(Date))';
let vMaxMonth = '=month(max(Date))';
let vMaxYear = '=max(Year)';
let vPriorMonth = '=month(addmonths(max(Date),-1))';
let vPriorMonthYear = '=Year(addmonths(max(Date),-1))';
let vPriorYear = '=vMaxYear-1';
let vPriorYearDate = '=date(addyears(max(Date),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';
Obviously you will need to change the field names to match the date fields in your data model.
There is a Qlik Sense version of this application available for download here:
Qlik Sense App: Prior Period Comparison with Set Analysis
There are a number of other tutorials and downloadable examples on our website here:
If you have any questions please post them in the comments below.