Skip to main content
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
Not applicable

Please upload PE enabled version.

Thanks in advance!

BR

0 Likes
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Unfortunately I am unable to make the document PE enabled.  I will seek to find if there is a way I can have someone else convert the file.

Steve

0 Likes
Not applicable

Hi Steve,

It's really helpful and I managed to do it without having QV Desktop. It's great that you have mentioned the variables in the post. However, vPriorYearDate variable is not giving leap year Feb 29 date. If we select Feb 2013 or Feb 28, 2013, its not giving Feb 29, 2012. I believe normally we are comparing YTD current year and YTD prior year, then below will do the work

=monthend(date(addyears(max(Date),-1),' MM/DD/YYYY' ))

I am newbie. Waiting for your valuable comments!

Not applicable

Justo lo que buscaba! Excelente!

Not applicable

Hi Steve,

Any idea to create a table, rolling by months selected by users...But one of the expression is to calculate the sales for Last year - Sales (LY). Refer to image below, you can see the last year figures went off to Nov-12 and Dec-12, any ways to park the Nov12 and Dec12 values in Nov-13 and Dec-13 respectively?

2.PNG.png

datanibbler
Champion
Champion

Hi Steve,

excellent post!

Really, period-over-period comparisons of KPIs are the bee's knees when it comes to managing the process. That is something that would greatly enhance the value of any QlikView_app
Best regards,

DataNibbler

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

You need a separate dimension of Month, without the year, so that things stack up.

Once you have done this though you will need to be very careful to limit the years using Set Analysis, or you could have values from before 2012 getting into those columns.

In your load script you should be able to have:

MonthYear,

Month(MonthYear) as Month,

Steve

Not applicable

@Steve Dark

Hi,

Can you please advise how to get last five years YTD sum on bar chart, where the dimension is year. The problem is if I select month, then it is giving correct YTD for the last max year only and then full year sum for the last years when using 2nd formulas as below.

I am using two formulas:

1) To get last five years sum

Sum({$<Year={">=$(=Max(Year)-4)<=$(=Max(Year))"}>} Sales Amount)

2) Then to get YTD, I modified above formula as:

Sum({$<Year={">=$(=Max(Year)-4)<=$(=Max(Year))"},Month=,Quarter=,Date={"<=$(=MonthEnd(max(Date)))"} Sales Amount)

Thanks in advance!

BR,

SAK

0 Likes
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Off the top of my head; you probably need to create a new field in your data model for 'Day Number In Year'.  You can calculate this by doing this in the load script:


     num(Date - YearStart(Date), '#,##0') as [Day Number In Year],

If you create a new variable with the name vMaxDayNumberInYear, with the following in it:

     =num(max(Date) - YearStart(max(Date)), '#,##0')

You can then use this in your Set Analysis across a year dimension and all should be well.

Hope that helps,

Steve

0 Likes
Not applicable

Hi Steve,

Thanks for reply.

It's great! But there is one problem - it is not considering a leap year. I put year as dimension and vMaxDayNumberInYear as expression. When I select Year 2013 and month Feb, the variable is being calculated as 59 (31+28) for all years, that is 2011 to 2014. However, it should be 59,60,59,& 59 for 2011,2012,2013,& 2014 respectively.

Please advise.

Thanks!

SAK

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