Point In Time Reporting Out Of The Box

Inicio/Point In Time Reporting Out Of The Box

Point In Time Reporting Out Of The Box

Visit the QlikOn Learning CenterUpdate 2014/03/24: We’ve launched an Online Training Center, a place where QlikView users, developers and enthusiasts can share and learn new skills with top-quality QlikView courses. One of the courses is on Set Analysis. If you want to learn more about how to use this powerful functionality, then please look at the course description here and Join us in one of our upcoming live, online sessions!

 

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:

  1. 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.
  2. 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:

  1. If you don’t declare your initialization variables (vDateMin and vDateMax), a Message Box will warn you and then the execution will end.
  2. 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.
  3. 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.

By | 2017-05-23T10:56:38+00:00 January 1st, 2011|Goodies, Set Analysis, The Magic Of Set Analysis|40 Comments

About the Author:

Consultor Experto QlikView. Autor del libro “QlikView 11 for Developers” y su versión en español “QlikView 11 para Desarrolladores”, publicados en noviembre de 2012 y diciembre de 2013, respectivamente. También he colaborado con la editorial, PacktPublishing, como revisor técnico en otros libros sobre QlikView. Descarga mi presentación de servicios.

40 Comments

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

    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.

    • Mike January 13, 2011 at 11:31 am - Reply

      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.

    • Murat Akkemik January 19, 2014 at 11:24 am - Reply

      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

  2. Rob Wunhderlich February 4, 2011 at 1:33 am - Reply

    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

    • Mike February 4, 2011 at 6:13 am - Reply

      Rob,

      Thanks for leaving your comment and I’m glad you liked the technique and used it already.

      Miguel.

  3. Nick February 24, 2011 at 3:03 pm - Reply

    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

    • Mike February 25, 2011 at 12:49 am - Reply

      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.

  4. DV June 13, 2011 at 7:43 am - Reply

    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

  5. DV June 13, 2011 at 7:45 am - Reply

    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

  6. Jason Michaelides July 6, 2011 at 2:59 am - Reply

    Fantastic resource! Thanks Mike.

    Jason

  7. mag August 20, 2011 at 10:30 pm - Reply

    Very well explained. Thanks for the post.

  8. Alex October 25, 2011 at 1:28 pm - Reply

    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

  9. Ilaria January 16, 2012 at 9:04 am - Reply

    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!

    • Mike January 16, 2012 at 11:29 pm - Reply

      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.

  10. Ilaria January 19, 2012 at 11:04 am - Reply

    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

  11. Brij April 12, 2012 at 12:54 pm - Reply

    Good one for beginers………

  12. Martin May 13, 2012 at 5:51 am - Reply

    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

  13. Andrey July 18, 2012 at 6:17 am - Reply

    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. 🙂

    • Mike July 18, 2012 at 9:02 am - Reply

      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.

      • Andrey July 19, 2012 at 9:23 am - Reply

        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

  14. Andrey July 18, 2012 at 6:39 am - Reply

    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

    • Mike July 18, 2012 at 9:05 am - Reply

      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.

  15. Cindy October 17, 2012 at 8:14 am - Reply

    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

    • Cindy October 17, 2012 at 8:15 am - Reply

      I want to say TeamBreakout={‘Yes’}

    • Mike October 28, 2012 at 11:29 am - Reply

      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

  16. Paul August 6, 2013 at 3:15 pm - Reply

    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

    • Paul August 6, 2013 at 6:13 pm - Reply

      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.

  17. Chuck December 2, 2013 at 11:47 am - Reply

    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.

    • Mike December 2, 2013 at 11:54 am - Reply

      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

  18. Murat Akkemik January 19, 2014 at 8:32 am - Reply

    Hi Mike,
    It was great job.
    can you give some advice about week sales too.
    kind regards
    Murat

    • Mike January 19, 2014 at 8:36 am - Reply

      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

  19. Murat Akkemik January 19, 2014 at 11:21 am - Reply

    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

  20. Brandon February 4, 2014 at 8:05 am - Reply

    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

    • Mike March 30, 2014 at 5:40 pm - Reply

      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

  21. Alex M. February 28, 2014 at 6:47 am - Reply

    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

    • Alex M. February 28, 2014 at 7:58 am - Reply

      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

      • Mike March 1, 2014 at 9:19 pm - Reply

        Hi Alex,

        I’m glad you’ve found a solution and thanks for sharing!

        Mike

  22. Ishaq April 21, 2015 at 3:37 am - Reply

    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

  23. Jacques Steyn May 22, 2015 at 3:00 am - Reply

    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

    • Mike May 23, 2015 at 7:54 am - Reply

      Thanks Jacqes! Glad you’ve found this useful.

      Mike

Leave A Comment

Sign up to receive updates on new articles

x

If you like this article, consider subscribing to our mailing list to receive updates of new posts right in your email inbox