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

[#2]

Please upload PE enabled version.

Thanks in advance!

BR

[stevedark]

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

[#4]

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!

[#5]

Justo lo que buscaba! Excelente!

[#6]

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?

[datanibbler]

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]

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

[#9]

@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

[stevedark]

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

[#11]

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