Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Qlik Sense App: Prior Period Comparison with Set Analysis

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

Qlik Sense App: Prior Period Comparison with Set Analysis

Last Update:

Aug 23, 2019 10:38:27 AM

Updated By:

stevedark

Created date:

Oct 10, 2016 3:57:50 AM

Attachments

This document shows how Set Analysis can be used to create a prior period comparison that is responsive to your current selections.

 

The expressions can be applied to any data set where you have your main date field broken out into Day, Month and Year fields.

 

This application is a Qlik Sense version of the QlikView app that has been uploaded to Community here:

QlikView App: Set Analysis - Prior Period Comparison

 

There is further discussion on the app and technique in the comments there (including some important notes about when your data set doesn't have values for all dates).

 

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

http://www.quickintelligence.co.uk/

Labels (3)
Comments
Anonymous
Not applicable

Thanks Steve!!!

thkarner
Partner - Creator III
Partner - Creator III

Hi Steve, thanks for the app. As I can see it uses the (good old) QlikView logic.

Is there a certain reason why you´re not using the pre-built derived autocalendar for date fields and the generated fields for period selections/comparisons?

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Thomas,

You have reasoned correctly that the app was created in QlikView first, and has been subsequently ported to Qlik Sense.


That said though, I will tend to load my dates in that inline fashion anyway, even in Sense.  Part of the reason is performance - with the autocalendar date variants are created when the user is interacting with the app rather than at load time - this is a performance hit.  Also, I don't like the large number of fields that are created and the way they clog up the field list.  You can edit the autocalendar to trim down the fields created (or add others, if you wish) but I find creating them in-line means that you tend to only have the fields you want in the format you want them.

thkarner
Partner - Creator III
Partner - Creator III

Understand. The only thing you probably loose is the zoom in/out function in the time aware line chart, which users like.

Regarding performance of autocalendar: You´re right, this could be a real issue! QS obviously does a complete LOAD RESIDENT of the table with the date field which takes time. To avoid this, you could create a linked dimension table containing only the single date field. This table has only some rows. In this case performance is no issue using autocalendar.

You could also see some details/findings in this post Derived Calendar Fields

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Thanks for your comments, you are correct about the auto zoom feature.  I have to admit that I find that you can get spurious results from that (such as when it shows times if you zoom to much even if your data only has daily granularity).

Adding an extra 'hop' in the data model would impact performance - but only a little.

If a RESIDENT load of all dates is done with the autocalendar then it must be that it is no longer doing things as a calculated dimension (it was many versions ago when I heard this was the case).  This will be an impact on the load script, but will not be too detrimental to users.

thkarner
Partner - Creator III
Partner - Creator III

Agree. What I normally do is using "best of all", means creating my own autocalendar with only those fields I want. Own autocalendar is also presented correctly in the line chart with continous timeline.

The script I´m using is:

[Cal]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

  Month($1) AS [Month] Tagged ('$month', '$cyclic'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

//  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

//  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

//  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

//  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

  Year(Today())-Year($1) AS [YearsAgo] ,

//  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

//  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

  Month(Today())-Month($1) AS [MonthRelNo]

//  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

//  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

//  Week(Today())-Week($1) AS [WeekRelNo]

;

DERIVE FIELDS FROM FIELDS [Date] USING [Cal] ;

writetoprithvi
Contributor II
Contributor II

awesome. I am migrating from Tableau and Power BI to QlikSense. Your file teaches so much about how to define variables and measures. Great stuff. Very clean.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Thank you, that's very kind. I have a number of other examples up on my website.

I'm hoping to share, within a week or so, a new app which will make moving things across super easy.

Take a look at this preview:

Keep an eye on my blog (or subscribe) to be notified when the app becomes generally available.

Steve

 

writetoprithvi
Contributor II
Contributor II

Thanks. Signed up for your blog.

dwighttrumbower
Creator
Creator

This is great information as I have been struggling with these type of sales calculations. This helps me tremendously. 

Now I need to figure out current QTD and previous year QTD. 

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