The Magic of Set Analysis – Point In Time Reporting

Inicio/The Magic of Set Analysis – Point In Time Reporting

The Magic of Set Analysis – Point In Time Reporting

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!

 

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

[Month (#)] which will contain the numeric representation of the field Month. That way, we can assign the result of the expression Max([Month (#)]) – 1 to the field [Month (#)].

Our preliminar expression

The Set expression to assign the obtained value to the field [Month (#)] would be:

  • {$<[Month (#)] = {$(=Max([Month (#)]) – 1)}>}. (Check here if you are having trouble understanding the syntax).

The next step would be to use this set expression with our Aggregation function:

  • Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}>} Sales)

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?

A few adjustments

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:

  • {$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Month = >}

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:

  • Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Month = >} Sales)

Another example

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:

  • Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Quarter = , Month = >} Sales)

Yet another inconvenient with the expression above.

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:

  1. Ignore both Year and Month selections.
  2. Create an expression that works just as the previous one, but with which we can also obtain the number 12 (for december) whenever the selection is January. (The previous expression (Max([Month (#)] – 1)) returns zero in this scenario, which is not useful.
  3. Create an expression that returns the value for the previous Year. Simple task.

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:

  • In the script, assign a consecutive number for each month, a unique ID, one that you can easily play with. I use the following formula:
    • (Year(Date) – 1)  * 12 + Num(Month(Date))

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:

  • Max(MonthID) – 1

The final expression

Our final expression will look like:

  • Sum({$<MonthID = {$(=Max(MonthID) – 1)}, Year = , Quarter = , Month = >} Sales)

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.

More Examples

Now, lets see some common Set Analysis expressions that we can use when required:

  • YTD (Year-To-Date) Sales:
    • Sum({$<MonthID = {“<=$(=Max(MonthID))”},
      Year = {$(=Max(Year))},
      Quarter = ,
      Month = >} Sales)
  • QTD  (Quarter-To-Date) Sales:
    • Sum({$<MonthID = {“<=$(=Max(MonthID))”},
      QuarterID = {$(=Max(QuarterID))},
      Year = ,
      Quarter = ,
      Month = >} Sales)
  • MTD (Month-To-Date) Sales:
    • Sum({$<MonthID = {$(=Max(MonthID))},
      Year = ,
      Quarter = ,
      Month = >} Sales)
  • Previous Month Sales:
    • Sum({$<MonthID = {$(=Max(MonthID) – 1)},
      Year = ,
      Quarter = ,
      Month = >} Sales)
  • Previous Quarter Sales:
    • Sum({$<QuarterID = {$(=Max(QuarterID) – 1)},
      Year = ,
      Quarter = ,
      Month = >} Sales)
  • Sales for the same Month but Previous Year:
    • Sum({$<MonthID = {$(=Max(MonthID) – 12)},
      Year = ,
      Quarter = ,
      Month = >} Sales)
  • Sales for same Quarter of the Previous Year:
    • Sum({$<QuarterID = {$(=Max(QuarterID) – 4)},
      Year = ,
      Quarter = ,
      Month = >} Sales)
  • YTD Sales for Previous Year
    • Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},
      Year = {$(=Max(Year) – 1)},
      Quarter = ,
      Month = >} Sales)
  • Sales for Rolling 12 Months:
    • Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”},
      Year = ,
      Quarter = ,
      Month = >} Sales)

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.

Mike.

 

By | 2017-05-23T10:56:38+00:00 November 27th, 2010|Set Analysis, The Magic Of Set Analysis|26 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.

26 Comments

  1. Barry December 2, 2010 at 10:17 am - Reply

    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

    • Mike December 3, 2010 at 7:52 pm - Reply

      Thank you Barry! Your comments and tips are always welcome.

  2. […] 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: […]

  3. […] 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. […]

  4. paul February 5, 2011 at 4:01 am - Reply

    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

  5. […] you know already, I’m a big fan of using Set Analysis in my QlikView applications, especially for Point In […]

  6. Stacy June 6, 2011 at 2:59 pm - Reply

    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

    • Cotiso September 4, 2013 at 9:36 am - Reply

      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

  7. Paul October 18, 2011 at 7:20 am - Reply

    As someone just starting to get their teeth into Set Analysis this is invaluable – Thank you

  8. Nick November 3, 2011 at 3:42 am - Reply

    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)

    • Mike November 3, 2011 at 8:27 am - Reply

      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

  9. Alex November 22, 2011 at 8:55 am - Reply

    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??

    • Mike December 8, 2011 at 8:56 am - Reply

      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.

  10. Alex November 22, 2011 at 9:28 am - Reply

    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)

  11. Chris June 20, 2013 at 12:50 pm - Reply

    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!

    • Mike August 30, 2013 at 6:23 pm - Reply

      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

  12. Håkan January 8, 2014 at 1:40 am - Reply

    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

  13. Håkan January 8, 2014 at 1:44 am - Reply

    I point to my post in the community:

    http://community.qlikview.com/message/444898#444898

    • Mike January 11, 2014 at 1:08 pm - Reply

      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

      • Håkan January 13, 2014 at 1:31 am - Reply

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

  14. Bruno Silva June 12, 2014 at 10:11 am - Reply

    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.

    • Mike June 12, 2014 at 10:51 am - Reply

      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

      • Bruno Silva June 12, 2014 at 11:25 am - Reply

        Thanks Mike!
        I will look at it!
        Regards,
        Bruno Silva.

  15. FAbiola January 11, 2015 at 12:55 pm - Reply

    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

    • Mike January 11, 2015 at 1:00 pm - Reply

      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))

  16. Ishaq Baig April 14, 2015 at 7:12 am - Reply

    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

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