Point In Time Reporting Out Of The Box

January 1, 2011 at 9:39 pm 24 comments

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.

Download Now!

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.

About these ads

Entry filed under: Goodies, Set Analysis, The Magic Of Set Analysis. Tags: Goodies, Point in Time Reporting, Set Analysis, The Magic of Set Analysis.

The Magic of Set Analysis – Point In Time Reporting QlikView mobile: AJAX Touch Client

24 Comments Add your own

1. MiCo | January 13, 2011 at 5:27 am

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.
Reply
- 2. Mike | January 13, 2011 at 11:31 am
  
  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.
  Reply
3. Rob Wunhderlich | February 4, 2011 at 1:33 am

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
Reply
- 4. Mike | February 4, 2011 at 6:13 am
  
  Rob,
  
  Thanks for leaving your comment and I’m glad you liked the technique and used it already.
  
  Miguel.
  Reply
5. Nick | February 24, 2011 at 3:03 pm

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
Reply
- 6. Mike | February 25, 2011 at 12:49 am
  
  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.
  Reply
7. DV | June 13, 2011 at 7:43 am

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
Reply
8. DV | June 13, 2011 at 7:45 am

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
Reply
9. Jason Michaelides | July 6, 2011 at 2:59 am

Fantastic resource! Thanks Mike.

Jason
Reply
10. mag | August 20, 2011 at 10:30 pm

Very well explained. Thanks for the post.
Reply
11. Alex | October 25, 2011 at 1:28 pm

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
Reply
12. Ilaria | January 16, 2012 at 9:04 am

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!
Reply
- 13. Mike | January 16, 2012 at 11:29 pm
  
  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
  You need to duplicate this line, but change the field name to “Payment_Date”.
  
  I hope this helps.
  Cheers,
  Mike.
  Reply
14. Ilaria | January 19, 2012 at 11:04 am

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
Reply
15. Brij | April 12, 2012 at 12:54 pm

Good one for beginers………
Reply
16. Martin | May 13, 2012 at 5:51 am

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
Reply
17. Andrey | July 18, 2012 at 6:17 am

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.
Reply
- 18. Mike | July 18, 2012 at 9:02 am
  
  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.
  Reply
  - 19. Andrey | July 19, 2012 at 9:23 am
    
    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
20. Andrey | July 18, 2012 at 6:39 am

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
Reply
- 21. Mike | July 18, 2012 at 9:05 am
  
  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.
  Reply
22. Cindy | October 17, 2012 at 8:14 am

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
Reply
- 23. Cindy | October 17, 2012 at 8:15 am
  
  I want to say TeamBreakout={‘Yes’}
  Reply
- 24. Mike | October 28, 2012 at 11:29 am
  
  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
  Reply