Qlik Sense App: Prior Period Comparison with Set Analysis

[stevedark]

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/

[stevedark]

Hi @dwighttrumbower 

Thanks for your comment. Hopefully you should be able to suss out quarters as well.

The thing to note is that there is no quarter function, so you need to calculate quarters thus:

'Q' & ceil(Month(Date)/3) as Quarter,
(Year(Date)*100) + ceil(Month(Date)/3) as [Year Quater],

Or similar, depending how you want things to appear.

Good luck!

[jbrooks181]

@stevedark I'm using your example and trying to apply it to my data set but for some reason when I bring the calculations into the table I get a null and I'm not quite sure why. The variables are showing values. The "label" on the calculation is showing the correct date that I would expect but when it comes to showing the actual metric value it's null. 

CurrentMTD: 

Load Statement: 

LOAD conn_id,
partition_ts,
year(partition_ts) as partition_year,
month(partition_ts) as partition_month,
day(partition_ts) as partition_day,
Date(monthstart(partition_ts), 'YYYY-MM') as MonthYear,

partition_ts is my date field. I'm stumped, hoping you can help me out

[stevedark]

Hi @jbrooks181 

Set Analysis is very picky about date formats. This is slightly odd, as dates are just numbers that are dressed up a bit, but there you go. It also tends to use the text representation of Dual values.

I presume that if you do a filter pane with partition_month you get Jan ... Dec? Make sure that if you display the variable vMaxMonth it also shows as three letters, e.g. Jul. If it shows as a number, you could try removing the single quotes from around it in the set analysis, but that may or may not work.

You could create a separate column by doing this, and that might be more reliable in set analysis:

1*month(partition_ts) as partition_monthno,

As a general rule of thumb, to validate set analysis it is a good idea to create a table which includes all of the columns referenced in the set analysis as dimensions. This way you can check that values only show on the rows you want them to. If you don't get the correct value from the set analysis measure create a separate measure which has the aggregation without the set analysis in it, you can then try parts of the set analysis until you work out which bit isn't working.

Feel free to post back with screenshots of this process if it doesn't get you to an answer.

Good luck!

Steve

[jbrooks181]

@stevedark Not sure why this is working now but I switched the partition_day to just "Day" in the equations and it seems to work now. partition_ts is a date field in the database, not a timestamp. Can you potentially explain what is occuring here? My understanding is "partition_day" should work. It feels like the expression is working but it's wrong given that it's not using the field in my load statement.

For context, I'm pretty new to qlik. I'm a Tableau guy. Appreciate your responses!

[stevedark]

Hi @jbrooks181 

The reason that changing partition_day to Day in the set analyis works is that parts of set analysis which do not match fields in the data model are ignored, rather than raising errors. The Day= part is doing nothing. This is fine in the current month, as there will be no values past the max day, but it will give the full month in the previous month rather than MTD.

Try changing Day back to partition_day and change the single quotes for doubles around the max day variable. It may be treating the result as a string literal rather than an expression.

Things to do to help test... add partition_day as a dimension into your test table, you can then see which days are included (you will currently see it goes above 13 in prior MTD). Also, try not using the variable in the set analysis, to check what works, and then when that works put the variable back in. So instead of:

partition_day={"<=$(vMaxDay)"}

Try with:

partition_day={"<=10"}

Once that is working (or if it works) go back to the variable. If that breaks there is something wrong in the variable (though the screenshot suggests that isn't the case).

Just another observation, is it correct that you are averaging the percentages of all the days, rather than working out the overall percentage from the total of the parts for all days? The results will probably be in the same ballpark, but skew could cause problems.

Hope that helps,

Steve