QlikView App: Set Analysis - Prior Period Comparison

[stevedark]

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:

https://www.quickintelligence.co.uk/qlikview-examples/

If you have any questions please post them in the comments below.

Steve Dark

www.quickintelligence.co.uk

[richho_microp]

Thanks Steve, this helped immensely.

[#3]

Brilliant discussion Steve, very helpful!!!

[kkkumar82]

Hi steve got struck in set analysis with pivot table combination, could u help me in figuring out this.

http://community.qlik.com/message/710454#710454

[#5]

question, where are you going to put these code?

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) & ')';

Is it on script? and how? is there any syntax on it?

[stevedark]

Hi Lester,

Copy and paste into your load script and then reload.  This will then create the variables that are used by the prior period app in another application.  You can then copy and paste the objects into your app.

The only other thing you need to do is match up the field names, ensuring that you have the same values as in my example (or change the variable code).

[#7]

okay then, thank a lot

[ecolomer]

Good post

[stevedark]

Thanks Enrique.  There are plenty more like it if you look here:

http://www.quickintelligence.co.uk/qlikview-examples/

Steve

[jlcoombs]

This example helped me solve my problem I posted here. Set Analysis Current Month vs Previous Month

Thanks Steve!

[#11]

Steve - thanks for this, I used your example and was able to achieve the same results, however when I add a dimension (by MonthYear) I don't get rolling results as expected. Rather, I only get results for latest month, previous month...etc. Any suggestions?

Thanks!

Adam