Hello and Happy New Year! Today we continue with ‘The Magic of Set Analysis’ Series and, in this post, I will share with you a “goodie”, something that has helped me speed up the Development process on new projects, and is specially useful when creating SiB’s (Seeing Is Believing) or Prototypes for prospective clients, where the time is often short. Point In Time Reporting has always been a must-have in every App I develop and, at first, the adaptation for every new application took me precious time to get it set, often reviewing the expressions I had previously used in other apps to adapt them to the one in hand. Then I realized it is basically, sometimes exactly, the same no matter the requirements, no matter the Client’s Line Of Business, it always came down to the same Set expressions. So, I made a “portable”, reusable script that helps me in every new project. It is contained in a text file, and I call it using the include statement in the QlikView Script. That’s it! One line of script and I have everything I need to start creating the Charts and Point In Time Analyses. How it works:
- It creates a Master Calendar, based on two variables you define: Start Date and End Date. This Table will populate some necessary fields to make the next part work.
- It creates a few variables (nine), that I call “Set Variables”, and that are used to build the expressions in our charts.
Once we run the script, we can start building charts with simple expressions like:
- Sum($(vSetYTD) Sales) – This will render the Sales Year To Date.
- (Sum($(vSetYTD) Sales) / Sum($(vSetPreviousYearYTD) Sales)) – 1 – This will return the YTD growth compared to Previous Year.
As you can see, it is very straightforward. The Set portion of the Expression is stored in a variable, and that conveniently help us if we need to modify something in the expression: we change it in the variable itself and will be applied to all the other expressions using it. Things to keep in mind:
- If you don’t declare your initialization variables (vDateMin and vDateMax), a Message Box will warn you and then the execution will end.
- 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.
- You can always adapt it to your needs.
Please download the following file, that contains a qvw with a Demonstration of how the script works and the text file itself. Also there is a sample CSV with Sales Data so you can reload the Demo qvw.
Please leave your comments/suggestions in the area below. I appreciate your feedback. Finally, I wish you all the Greatest Success in 2011! Happy Qliking.
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. Mike.
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.
Qlik Set Analysis Cheat Sheet. By Miguel Ángel García
Great reusable technique for PIT Reporting.
I have the followng remark about your DEMO qvw:
when i select a 1 Year a 1 Month value and NO Date values, then I expect the “Sales MTD” value to be equal to the “Sales based on current selections” value.
This is however not the case for example Jan 2008, Feb and Mar 2008 work correctly.
Changing your vSetMTD variable to
{$<MonthID = {"=max(MonthID)"},
Date = {"}
seems to do the job.
Hello Michael,
Thank you for passing by and leaving your comment. I did an exhaustive test for each and every one of the 36 months (July 2006 – June 2009), and the results were correct for all of them. I don’t really know if I understood you correctly, or if I’m missing something… Either way, Thank you for also providing a solution.
Mike.
Dear MiCo,
I think there is a problem in your script when I selected 2008 or 2007 years AND 2nd month (Feb). Previous year,month and quarter were not shown as empty but not!
2009 Feb is working normally. There is problem in FEB selection, why?
I did your corrections as well but not worked too.
Could you please give your comment and write the exact code pls.
regards
Murat Akkemik
Hi Michael,
This is an excellent reusable toolkit for the Community. Thanks so much for sharing it. i used it at my most recent SiB.
-Rob
Rob,
Thanks for leaving your comment and I’m glad you liked the technique and used it already.
Miguel.
Hi Mike,
I have been using this scrpt and it has been extremely handy.
I have been palying around with adding some script to account for different fiscal year calculations, with no luck.
Any suggestions.
Thanks and have a great day!
Nick
Hello Nick!
I’m glad you have found it useful. About the Fiscal year calculations, I think I have the another version adjusted just for that purpose. Let me check that tomorrow and if I do, I will upload it and let you know.
Have a good weekend!
Mike.
Hi Mike,
I got hold of your blog through Steve & Barry’s blog. Thanks so much for detailed tutorials on Set Analysis. I had already subscribed and look forward for more posts. Meanwhile, please can you let me know where can I declare the vDateMin and vDateMax in the script? I am little confused on this piece of information. Also, please can you expand these articles on Indirect Analysis, Possible & Excluded selections within Set Analysis?
Many thanks in anticipation.
Cheers – DV
Please ignore my previous comment on the – declaring the vDateMin and vDateMax variables in the script. I had read your post again and got it working. Sorry for the trouble.
Thanks – DV
Fantastic resource! Thanks Mike.
Jason
Very well explained. Thanks for the post.
Would any be able to help me out with this situation?
I’m trying to compare this year’s day vs. last years day.
I want to adjust the current year to match up against last years day.
For example,
9/30/2010: Thursday – Last Year
9/29/2011: Thursday – Current Year
Would there be an easy way of doing this?
Thanks, Alex
Hi Michael,
I am new to Qlik View and I have appreciated a lot your example for point in time reporting.
I would appreciate if you could help me with this problem.
If I have a Date in my database, let us call it “Payment_Date” and I want to refer all my time analysis to that date, how should I change the script or the external file?
The idea is that my YTD, MTD etc. functions have to work on the field “Payment_Date”.
Any help will be very much appreciated.
Thank you!
Hello Ilaria!
In the external file, look for the part in which the Date Field is defined within the Master Calendar table and copy that line. It goes something like
Date($(vDateMin) + IterNo() – 1, ‘$(DateFormat)’) as Date,
You need to duplicate this line, but change the field name to “Payment_Date”.
I hope this helps.
Cheers,
Mike.
Dear Mike,
thanks for your reply!
I duplicated the line and added it to the script, in the master calendar, after the same line, i.e.
LOAD
Date($(vDateMin) + IterNo() – 1, ‘$(DateFormat)’) as Date,
Date($(vDateMin) + IterNo() – 1, ‘$(DateFormat)’) as Payment_Date
… etc.
but the result I get is that, when I use the YTD function I get the same numbers as for this year.
(with YTD function I mean “Sum($vSetYTD) expr)”
I’ve also tried to remove the first load and leave only the second one, with my Payment_Date but I get the same results.
Moreover it seems that the upload of the data from SQL Server leaves out one of the year, i.e. the most recent year 2012.
Thank you for your help.
Ilaria
Good one for beginers………
Hi Mike, I’m reasonably new to Qlikview but you have really helped me with my understanding of Set Expressions and scripting. I can immediately see how this “include” method can save hours of work. Thank you for sharing this. I am currently struggling with a client who needs reports based upon both calender to fiscal year (August – July). How simple would it be to adapt this script to work for both? Martin
Hi, Mike!
Thank you very much for your posts! They are very helpful.
is it possible to add to your example this expression: (Sum($(vSetYTD) Sales) / Sum($(vSetPreviousYearYTD) Sales)) – 1.
When i try to do it by myself if returns nothing.
If we look at first part of this expression “(Sum($(vSetYTD) Sales) “, we can see that QlikView underline the word “Sales” with red. I believe Qlikveiw indicates the mistakes in that way. So the second part of the expression “/ Sum($(vSetPreviousYearYTD) Sales)) – 1” can’t be read by the program.
What i did wrong? How can i fix that?
And one more question: if i want to get the MAT growth compared to Previous Year MAT i need something like:
(Sum($(vSetRolling12) Sales) / Sum($(vSetPreviousYearRolling12) Sales)) – 1
so how can i get this “vSetPreviousYearRolling12”?
i suggest it might look like:
{$=$(=Max(MonthID) – 23)}
i hope i describe my problem clearly.
Thanks again for you time and patience. 🙂
Hi Andrey,
Don’t worry about the red underline. I mean, yes, it’s there because QlikView doesn’t recognize the syntax, but the expression will be evaluated correctly even so.
What I suggest for you to fix the problem is 1) Make sure the Master Calendar table contains all the required fields. 2) Make sure the vSet* variables use the field names as they exist in the Master Calendar Table. 3) Try debugging the expression one part at a time to identify which variable is not being correctly evaluated.
About the vSetPreviousYearRolling12 variable, you would have to create it based on the ones that are already defined. It’s pretty simple, you’ll see.
Regards,
Mike.
Mike, thanks for your help.
1) yes, the Master Calendar table contains all the required fields
2) the vSet* variables use the field names as they exist in the Master Calendar Table.
My expression still not working.
3) What do you mean by debugging the expression? how can i do that?
“……Chart expressions are not debugged, as they are in an object, out of the scripting process…….” Qlikview community
I’ve found next solution.. if i replace in variables with full expression e.g. sum( sales) then it works fine. I guess it’s not the best solution.
Best regards,
Andrey
regarding “vSetPreviousYearRolling12″…. it didn’t show the full expression for some reason.
So instead of “{$=$(=Max(MonthID) – 23)}” i meant this:
“{$=$(=Max(MonthID) – 23)}”
Thank you,
Andrey
Don’t worry, it does that sometimes. Like I said, base your new set variable on the already created vSetRolling12 variable.
Hope it helps.
Mike.
How do I add additional set analysis if I use this in my expression? Do I have to add it in the script or is there a way to add it in the expression?
Thank you,
Cindy
I want to say TeamBreakout={‘Yes’}
Hi Cindy,
If you want to add additional set modifiers in your expression, then the set variable should be modified to exclude the opening and closing symbols, and include those into the end expression.
Mike
Forgive my ignorance, but when I run this to build a calendar it does not work, I have tried various date formats for the Start/End date range e.g. ‘2008-07-01’.
What format should be used?
Thanks
Actually I’m now using a different way , instead of hard coding a start/end I just query my fact table for the date range.
I find this the most troubling thing about Qlikview – Set Analysis is great – but it leaves a HUGE hole. All the varaibles here (and Set Analysis) relies on 1 selected period.
I need to be able to refer to the Prior Period – regardless of a selected Period – for exmaple, I have a table of 10 years I need the 2 year to refer to the first year (like a year over year comparision). We have the BEFORE operator to allow this but it only works in the context of a chart or table that is displaying all the years. As soon as the orde changes – ir if you want to display the result in another table you lose context.
Set Analysis has not concept of the current row in a table. Some reporting and anaylsis is completing impossible with Set Analysis while some things are very easy.
Hi Chuck, Thanks for your comment.
I think we should always keep in mind that Set Analysis is not a panacea. Knowing about its “powers” and restrictions should help us determine in which cases it provides a good solution and in which cases an alternative should be looked for.
Good luck in finding a solution to your specific need.
Mike
Hi Mike,
It was great job.
can you give some advice about week sales too.
kind regards
Murat
Thank you Murat,
For weekly period analysis, you’d just need to create a WeekID field in the master calendar using an expression like: Autonumber(Week(Date)) as WeekID. Then, the set expression would be similar to the ones already created.
Kind regards,
Mike
Dear Mike,
I think there is a problem in your script when I selected 2008 or 2007 years AND 2nd month (Feb). Previous year,month and quarter were not shown as empty but not!
2009 Feb is working normally. There is problem in FEB selection, why?
Could you please give your comment.
regards
Murat Akkemik
Dear Mike,
Could you please provide the necessary script changes to modify this for a fiscal year starting in Nov-Jan Q1, Feb-Apr Q2, etc. This would be most helpful.
Best
Hi Brandon,
As much as I’d like to help, I’m not able to respond to this type of queries via the comment sections on my blog. The information provided here is a tutorial that, if followed, will allow readers to learn about how QlikView works in various aspects. However, it’s not meant as an exhaustive resource and is not expected to cover every single scenario that you may encounter. For this reason, and in case the resources published in this website are not enough for your learning needs, I suggest the following:
– Find help on QlikCommunity
– Read our book QlikView 11 for Developers which has more detail in this topic (http://iqlik.wordpress.com/get-the-book)
– Sign up for an online course on set analysis at http://q-on.bi/courses
Kind regards,
Mike
Dear Mike,
First of all many thanks for your help, I’m a newcomer to Qlikview so please forgive me if I’m asking something that’s obvious. I just laid my hands on your demo file and I found out that when I make selections through the Day listbox and selecting or not selecting any DATE, some expressions seems to not be working properly.
For example, I select Year = 2009, Month = Feb and Day = 10 and the file returns:
– Year to Date: From 10/01/2009 to 10/02/2009
– Month to Date: From 10/02/2009 to 10/02/2009
– Sales Month to Date: 48.395
(The results don’t change by selecting ’10/02/2009′ in the Date listbox)
But, If I make a selection through the Date listbox to 10/02/2009, the file returns:
– Year to Date: From 01/01/2009 to 10/02/2009
– Month to Date: From 1/02/2009 to 10/02/2009
– Sales Month to Date: 2.789.181
Please, could you give me some hint about it?
Many, many thanks for your blog and best regards,
Alex Millan
Think I’ve found what it was all about.
When defining the variables, the selection made in the [Day (#)] listbox were still active.
I’ve only had to insert ‘[Day (#)]=,’ in the troubling variable definitions to ignore this selection, and now everything works fine.
Thanks again for your blog and your help, without it I wouldn’t have been able to find it by myself.
Regards,
Alex Millan
Hi Alex,
I’m glad you’ve found a solution and thanks for sharing!
Mike
Hi Mike,
Excellent Work.
Can I get some advice about Previous Week and Previous Week Last Year. (I was able to get the Week Analysis)
Regards
Ishaq
Hi Mike – I’m new to QV and really appreciate you sharing this! Been researching a lot to try and find and understand the date and comparative analysis with QV.
Again – awesome and thanks!
Jaq
Thanks Jacqes! Glad you’ve found this useful.
Mike