Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Shoaib - glad this has taken you a step forward. In many ways just looking at the day of the year (regardless of leap year) will give you a more balanced comparison as you are always comparing the same number of days. Besides, things like number of weekends in the period to date will probably make far more difference than number of days.
That said, you can compare to the same day in each year by creating a number that relates to the day of the year - but is not consecutive between months (to allow for the 29th of Feb).
Calculate this number like this:
num((month(Date) * 100) + Day(Date), '#,##0') as [Day Number In Year],
This will represent the 1st of March as 301, regardless of whether there were 59 or 60 days before it.
The variable would need to be calculated the same way, just using the max(Date).
Hope that helps,
Steve
Hi Steve,
I know you're already aware of this piece of work (I'm sharing across relevant threads)
http://community.qlik.com/docs/DOC-6593
This can be used to do simple period analysis.
Regards
Richard
Steve
How would I get values of another field one year ago based on a date for this year.
example;
Ideas on how to store % of quarter completed to use for forecasting. I have done this before but it has bene years. Same for month and year periods.
we are at business day 23 of 64 thru the quarter this year so 23/64 or 36% of quarter completed with just 50,000 in sales.
Last year same quarter as of day 23 we sold 100,000 and finished at 200,000 so 36% into the quarter we had completed 50% of our finish. so if we were to use the logic of being 50% complete, then for this quarter we will only finish at 100k, which is half or our finish last year same quarter. I did this successfully over 6 years ago . I am hoping there is easier logic than I used (assuming it was more complex than needed as I was relatively new to qlik) . I know longer have access to that application.
Hi Debbie,
This certainly sounds feasible, using a number of Set Analysis building blocks. I would therefore put each of the relatively simple bits of Set Analysis into variables and you would have an expression like this to arrive at your full year projection for this year.:
$(vYTDThisYear) / ($(vYTDPriorYear) / $(vFullYearPriorYear))
The great thing about QlikView variables is that you can nest them, so all of that expression could go into another variable, say vProjectionThisYear, and you can work out the variance to last years full year figures like this:
$(vProjectionThisYear) / $(vFullYearPriorYear)
You just need to be really careful with the brackets when doing the dollar expansion, so I would recommend a set of brackets around the outside of any expression placed into a variable.
Hope that all makes sense.
Regards,
Steve
I find this very helpful. However when downloading your document and playing with it I am not able to select Aug-Sep and then find a comparison of last year Aug-Sep. How would I do this, as this allows for more dynamic comparison rather than having January always as starting period.
Hi Coen,
The whole point of the comparison here is that it is year to date. Just using the calendar for selecting dates is simpler, and doesn't even require Set Analysis. Simply create a chart with Year and Month as dimensions. You will get a line for each Year and Jan to Dec along the X axis. Selecting two months, such as Aug/Sep will compare those two months across years.
Hope that helps.
Steve
Hello Steve,
thanks for the explanation. I see that it works good that way. However I don't want to work without any set expressions because of user expectations that the current year will always show against the previous year.
Therefore I select 2014 automatically so that I can use automatically changing labels. This limits the month selection currently to Jan-Sep. These I use in my Expression Labels with the following string:
='Sales ' & Max(Year)
& chr(10) & MinString(Month) & '-' & MaxString(Month)
So that we get Sales 2014 Jan-Sep. This will always be accurate because of the set analysis.
=Sum({<Year = {'$(=Max(Year))'}>}VEH_SizeNetSales)
Now for the previous year I use the same label string, expect that I take the max year minus 1. So we get Sales 2014 Jan-Sep. But then comes the trick that I can't solve which is that I also want the maximum month to be the maximum month of the max(year).
Currently I have Sum({<Year = {'$(=Max(Year)-1)'}>}VEH_SizeNetSales) for the last year sales. Which will obviously result in the whole years sales.
So what I wanted is that with just loading the document all comparisons are available and correct including the labels.. Which will also be for whenever someone selects say Apr-Aug.
Currently it results in the output in the image: (where only year=2014 has been selected)
Hopefully you can shed some light on this!
Thanks in advance,
Coen
Hi Coen,
You also need to have set analysis on your month. I would move from nested expressions to having variables, eg:
vMaxYear: =Year(Max(Date))
vPriorYear =vMaxYear -1
vMaxMonth =max({<Year={'$(vMaxYear)'}>}Month)
vMinMonth =min({<Year={'$(vMaxYear)'}>}Month)
The set analysis would then be:
sum({<Year={'$(vMaxYear)'},Month={'>=$(vMinMonth)<=$(vMaxMonth)'}>}Value)
and
sum({<Year={'$(vPriorYear)'},Month={'>=$(vMinMonth)<=$(vMaxMonth)'}>}Value)
And the legend would be:
='Sales $(vMaxYear)
$(vMinMonth)-$(vMaxMont)'
This will then pick up all months between the max and min months selected - which would fit in with your legend.
Hope that helps. All code has been typed directly into the message and not tested, so there may be some syntax tweaks required.
Good luck!
Steve
Hello Steve,
Thank you for explaining it so well. I got it work and understand it! Just one more question is that my Month field actually contains Jan until Dec. However with the function
vMaxMonth =max({<Year={'$(vMaxYear)'}>}Month)
it sets a value of 10 (for October that is). Now for the sales calculation this is perfectly fine. However for the legend/label it would be strange to see the months in numbers. If I use maxstring instead of max it will give me the correct Month in letters. Is this the appropriate way of dealing with this? So I would create two more variables in order to display Months in letters.
Thanks ,
Coen
Yes, that would be fine, have a vMaxMonth and a vMaxMonthLgd variable. Glad you like the explanation!
Steve