Point In Time Reporting
I have always believed that Set Analysis’ Raison d’être is to satisfy a basic need in any BI Tool: the ability to perform “Point In Time” Analysis. But, needless to say, it is also amazingly useful for the fulfillment of a bunch of other special needs. In this post, I will cover the specifics of Point In Time Reporting with Set Analysis.
Update 2012/12/13: Barry Harmsen and I have just released our book QlikView 11 for Developers. While it is not free, it does offer a cost-friendly, guided way to learn QlikView and contains an entire chapter about Set Analysis. More information can be found by clicking here.
Step by Step
We have already reviwed Set Analysis Syntax here and the concept of Dynamic Record Sets here. So now, we will combine the acquired knowledge to deliver a real-life application of Set Analysis.
Let’s say the user has the following Selection State: Year = 2010; Quarter = Q4; Month = November. The user is analyzing Sales and needs to compare this period versus the previous Month (October, in this case). How do we accomplish this? This one is simple.
The basic idea is to tell QlikView to assign new value to the field Month. Remember how we did it in the previous post?
Determine the value to be assigned
We can use an aggregation function to determine the value we want to assign to the field Month, for example: Max(Month) – 1. Depending on the way you generated the field Month, it might give you the correct result (10, for October). But if it does not have a numeric representation, meaning it is just a literal value, it will return null.
However, even if it yields a numeric value, you can not assign that value to the field Month because it does not exist as is, we have to assign the exact value, as it exists in our table, to the appropriate field (‘October’ as a literal, to the field Month). So our approach will be to have another field, named The Set expression to assign the obtained value to the field [Month (#)] would be: The next step would be to use this set expression with our Aggregation function: But there is a problem! That expression will return zero because the value we are trying to retrieve is actually excluded by the current selection state of the user: The user has specifically selected November, therefore reducing the record set to only the records associated with that value. How do we retrieve a value that is currently excluded? The answer is: ignore the user’s selection for that specific field. This is accomplished by assigning a blank value to that field in our set expression: That will first retrieve the record set for all the available months, unless they are excluded by another field selection, and then assign the value we want (10) to the specified field ([Month (#)]), therefore returning at he end the record set for October only. The order of the field assignments in our Set Expression is not of importance. Our final expression is: Easy huh? Great! Now, Let’s say the user’s selection State is: Year = 2010; Quarter = Q4; Month = October. Will the above expression return the sales for September? The answer is No. Can you guess why? Correct! Because the month we want is not only excluded by the field selection for Month, but also by the field selection for Quarter. So our expression should be: When developing an application, you have to account for all the alternative selections the end user can make. So now, let’s assume the selection state is as follows: Year = 2010; Month = January; (Nothing selected in the field Quarter, but as we might expect, only one possible value (Q1). If we want the sales for the previous month (December, 2009) we need to: A direct approach would be to create an If Expression like this: If((Max([Month (#)]) – 1) = 0, 12, Max([Month (#)] – 1)). But I find it very impractical, so here is another approach: The above formula will for example, assign the value 24120 to December 2009 and 24121 to January 2010, and so on. Moreover, since these numbers are 5-digit long, you can easily find a more memory-friendly way to assign a unique ID to every month using the same concept. Our friend Barry, from qlikfix.com, recommended here to use the AutoNumber function. I second that, only if your calendar table is loading in ascending order based on the date, which is almost always the case. So, now that we have the consecutive number for month (let’s call it MonthID), we can use this expression to retrieve the value for the previous MonthID that will work for every scencario: Our final expression will look like: Note that I’m ignoring Selection made on Year, Quarter and Month. The same concept used in MonthID can be used for the field Quarter. We can easily and seamlessly create a lot of expressions for Point In Time Reporting after we’ve created the Calendar table with at least the following fields: Date, Year, Month, Quarter, MonthID and QuarterID. Now, lets see some common Set Analysis expressions that we can use when required: So, there you have it, a complete set of formulas to help you create Point In Time Analysis in your QlikView Applications in a very simple fashion. I hope you have enjoyed this Part IV of the Series ‘The Magic Of Set Analysis’. Next post: Create your Point In Time Reporting Set expressions once and reuse them in every new QlikView app. As always, comments and feedback are welcome. Miguel Ángel García Update 2019/04/25: If you found this useful, you may want to take a look at the Set Analysis Cheat Sheet and invaluable resource for Qlik Developers.Our preliminar expression
A few adjustments
Another example
Yet another inconvenient with the expression above.
The final expression
More Examples
Year = {$(=Max(Year))},
Quarter = ,
Month = >} Sales)
QuarterID = {$(=Max(QuarterID))},
Year = ,
Quarter = ,
Month = >} Sales)
Year = ,
Quarter = ,
Month = >} Sales)
Year = ,
Quarter = ,
Month = >} Sales)
Year = ,
Quarter = ,
Month = >} Sales)
Year = ,
Quarter = ,
Month = >} Sales)
Year = ,
Quarter = ,
Month = >} Sales)
Year = {$(=Max(Year) – 1)},
Quarter = ,
Month = >} Sales)
Year = ,
Quarter = ,
Month = >} Sales)Subscribe to receive new posts like this one and updates right in your inbox
Hi Mike,
Thanks for another excellent piece, very well explained.
Just to clarify on my AutoNumber comment, you are correct that this only works when you sort the calender in ascending order. I usually generate a calendar, so the sort order is not a problem then. A second important piece is to use different counters for each AutoNumber, for example:
AutoNumber(Week, ‘WeekCounter’) as WeekID,
AutoNumber(Month, ‘MonthCounter’) as MonthID
etc.
The need for separate counters is because, by default, the AutoNumber function returns an incrementing number from a single counter irrespective of the input field. In that case you cannot be sure that the difference between increments of an ID is always 1. By creating a separate counter for each ID you eliminate this risk.
Cheers,
Barry
Thank you Barry! Your comments and tips are always welcome.
[…] The Magic of Set Analysis: Point in Time Reporting This post is the 4th in a series about Set Analysis over at iQlik. It provides a well-written description of how to use Set Analysis to do point-in-time reporting in QlikView. If you are new to Set Analysis I recommend reading the previous 3 posts as well, as these will teach you the basics of Set Analysis in a structured manner: […]
[…] You need to review and understand clearly what the expression is doing before using it on your apps. It is intended to be simple though. If you are having trouble understanding them, please review my previous post. […]
Hi Mike , great job , you are super because you can make complicate expression into very easy understand expression. hope you can share with me my problem on above link.
http://community.qlikview.com/forums/t/40468.aspx
Paul
[…] you know already, I’m a big fan of using Set Analysis in my QlikView applications, especially for Point In […]
Hi Mike!
Your point-in-time blog is great, but I have a situation which I believe requires something a bit different. I need to show all months, with either actual totals (if the month is within 1 year of the current date) or the quarterly average from 5 quarters ago. Any suggestions?
Thank you,
Stacy
I would create a new DUAL time dimension, that has
– within the numerical part the date of the begining of the period you need and
– within the text part:
– for the last 12 months the month name and
– for the previous 3 periods “Q-5 Monthly Avergage” or whatever you feel like it sould pe presented there
This dimension can be created at least with an if statement, either in the script or in a calculated dimension of a chart, let’s say.
In the formulas of the required chart, instead of sum(Sales), you should have a formula like sum(Sales)/count(distinct RollingMonth)
where RollingMonth=monthstart(Date),
either as a calculated dimension (in script) or even pretty of
and Date is the transaction date field.
Hope it helps.
Sempre fi,
Cotiso
As someone just starting to get their teeth into Set Analysis this is invaluable – Thank you
Thank you for this unvaluable post, this was so useful for me.
However any idea how to show the period name (Month/Year) in the expression label?
Example: when 2011 and October is selected I want the label the Previous Month Sales expression to be “sep-2011” or “09-2011”
•Previous Month Sales:
◦Sum({$} Sales)
Hi,
Thanks for your comment.
You can construct the expression label using the same Set expression you used to calculate sales.
For example: max(—set expression for previous month— year) & ‘ – ‘ & maxstring(—set expression for previous month— MonthName)
In the above example, replace “—set expression for previous month—” with the actual set Analysis definition.
Hope this helps.
Mike
Hi Mike,
The YTD Sales for Previous Year is not working as expected for me. All the other calculations come at as expected, but my number for previous year is wrong.
This is the calculations that I’m using for 2010 numbers. But I want to change it to what you have provided.
Sum({$}Cnt)
The number I keep getting is lower than what this calculation gives me. What is the difference between the two??
Hi Alex, I would say you should validate the numbers on a date-by-date basis. Maybe the YTD calculation, due to its nature of “to this specific date”, might be excluding some days that you expect to consider.
Let me know.
Mike.
Okay, so when I change the MonthID expression to 10, instead of 12, I get the expected amount. But why would that make it correct??
Add it looks like I forgot to finish my expression above.
sum({$}Cnt)
First off, thank you so much for the PIT script–very helpful! Would you let us know how to modify the vSetRolling12 variable to use the last FULL 12 months? Thanks again!
Hi Chris,
Are you using the Set expression corresponding to “Sales for Rolling 12 months” as specified on this post? There is no condition applied to it that would limit the monthly data, it should result in the full 12 months.
Mike
I was very happy with this article and I have used it but having problem with the R12.
This works fine getting previous months, any month:
=SUM
(
{$}
Sales
)
A YTD calculation as well:
=SUM
(
{$<
MC_YearMonthID={"}
Sales
)
But this R12 gives me far to small numbers:
=SUM
(
{$=$(=Max(MC_YearMonthID)–11)}
Sales
)
Anybody that sees any syntax problems?
I use a master calendar with:
TempDate As MC_YearMonthDate,
Week(TempDate) As MC_Week,
Year(TempDate) As MC_Year,
Month(TempDate) As MC_Month,
ApplyMap(‘QuartersMap’, month(TempDate), Null()) as MC_Quarter,
(Year(TempDate)-1)*12+Num(Month(TempDate)) as MC_YearMonthID
I point to my post in the community:
http://community.qlikview.com/message/444898#444898
Hi Håkan,
The syntax is correct. Are you getting unexpected results or no results at all?
Try reviewing the results in a table with MonthID as dimension.
Regards,
Mike
Thank you for the response. If the syntax is correct I will have to dig deeper. I do get numbers so at least that is nice. 🙂
Hi.
Thanks a lot for this precious article. I´ve been using this for a few projects and works just perfectly. But now I need something that I wasn´t able to do. I´ve tried a few things and nothing…
In this particulary case, the time dimension in the data is just YearMonth, eg. 201404.
Current YTD formula demands a full date, and I don´t have it, as I explain it above. How can I adapt the existing YTD to this time dimension?
Thanks,
Bruno Silva.
Hi Bruno, Thanks for your comment.
If your data is at a monthly level, you need to make some changes to the calendar script as well as the corresponding set variables. Please take a look at the following sample script and adjust it according to your data: http://q-on.bi/go/h
If you’d like to learn more about set analysis, consider joining us next july 10th for an online course delivered by Barry Harmsen. More info: http://q-on.bi/go/i
Hipe this helps
Mike
Mike
Thanks Mike!
I will look at it!
Regards,
Bruno Silva.
Hi Mike , great job, Thanks for the post, very interesting.
I would like to report this my difficulty. After creating the script with your formula in the QVD file if I enter the formula (= MonthID) it give me correctly 24169 (ie year 2014) while if I enter the formula Max (MonthID) it gives me error … why? is a formatting issue of Dates in the script?
Fabiola
Hi Fabiola. Thanks for your comment.
In your expression, are you including the equals sign? It has to be included for it to be evaluated: (=max(monthid))
Dear Mike,
Thanks for the excellent article. Please let me know how to achieve the below scenario.
Based on your DEMO.qvw
For Example the Following values are selected
Year = 2008
Quarter = Q1
Month = Mar
How to achieve the below tables (The Year dimension needs to be shown. If the year dimension is removed the values are shown right but if the year dimension is selected then two years are shown 2007 for Last Year sales 2008 for current year sales)
Shown in this Way
Year Sales YTD Sales YTD Previous Year
2007 0 4899285
2008 5735449 0
Need to show in this format
Year SalesYTD SalesYTD (Previous Year)
2008 5735449 4899285
Table does not show the Cumulative sales But shows individual sales for each month for Sales YTD Measure
Year Month Sales YTD
2008 Jan 1,506,218
2008 Feb 2,413,818 (cumulative YTD Jan+Feb not shown)
2008 Mar 1,815,413 (YTD is not shown cumulative Jan+Feb+Mar)
Do I need to change the variable to achieve what I want. Your suggestions are are highly appreciated.
Thanks & Regards
Ishaq