Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

Qlik Sense App: Prior Period Comparison with Set Analsysis

MVP
MVP

Qlik Sense App: Prior Period Comparison with Set Analsysis

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/

Attachments
Comments
cyruslentin
New Contributor II

Thanks Steve!!!

thkarner
Contributor 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?

MVP
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
Contributor 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

MVP
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
Contributor 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] ;

Version history
Revision #:
1 of 1
Last update:
‎10-10-2016 03:57 AM
Updated by: