Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

QlikView App: Set Analysis - Prior Period Comparison

cancel
Showing results for 
Search instead for 
Did you mean: 
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

QlikView App: Set Analysis - Prior Period Comparison

Last Update:

Aug 23, 2019 10:43:02 AM

Updated By:

stevedark

Created date:

May 29, 2013 5:38:11 PM

Attachments

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

Comments
richho_microp
Contributor III
Contributor III

Thanks Steve, this helped immensely.

Not applicable

Brilliant discussion Steve, very helpful!!!

kkkumar82
Specialist III
Specialist III

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

0 Likes
Not applicable

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?

0 Likes
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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).

0 Likes
Not applicable

okay then, thank a lot

ecolomer
Master II
Master II

Good post

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

Steve

0 Likes
jlcoombs
Contributor II
Contributor II

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

Thanks Steve!

Not applicable

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

Contributors
Version history
Last update:
‎2019-08-23 10:43 AM
Updated by: