Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks Steve!!!
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?
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.
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
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.
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] ;
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.
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
Thanks. Signed up for your blog.
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.