10 Replies Latest reply: Apr 7, 2010 12:27 PM by Community Administrator RSS

    Set Analysis - Has anyone got a tutorial/examples of this?

    michaellee1

      Hi all,

      Is there a tutorial or examples of set anaylsis flying around? I need to be able to use today's date to calculate the accounting period that we are in and then using that answer, calculate how much sales we have using the answer as a filter.

      i.e Today is July 2nd, The accounting period that it is in is July (There is an accounting period table with dates and the date 02/07/2009 has the Period July). and I have to sum up the values in that period.

      I have 2 tables

      Acc Period

      Date Period

      26/06/2009 June

      27/06/2009 June

      28/06/2009 July

      ..

      ..

      24/07/2009 July

      25/07/2009 August

      Sales

      Date Value

      27/06/2009 100

      28/06/2009 200

      24/07/2009 150

      25/07/2009 100

       

      From the example there, I would expect my sum to equal 350.

      Could you please help?

      Thanks,

      Mike

        • Set Analysis - Has anyone got a tutorial/examples of this?
          Neil Miller

          I'm not really following your data there, so I'll just give a few good places to look.

          1. Right here. On the right hand side of the forum, you should see Popular Tags. Set Analysis is the largest. Click on that and get a list of all forum posts tagged as Set Analysis. Searching for Set Analysis and Date should get you some good examples.

          2. The Reference Guide. There is now a Set Analysis section at the end of Book II. That's how I learned the basics. I have it printed out and regularly refer to it when exact syntax slips my mind.

          I'm assuming you already have some Set Analysis going, maybe you could post what you have so far.

            • Set Analysis - Has anyone got a tutorial/examples of this?
              Oleg Troyansky

              Help section also has a nice article about Set Analysis with many helpful examples.

              Looking at your requirement, though, I think you should start at the script level, not within the expression. When you reload the data, it's very common to capture the "Current Date" into a variable. It's also pretty common to calculate Current Fiscal Year and CUrrent Fiscal Month.

              Many people stop there and then load their logic into their expressions - using either IF statements or Set Analysis. As a result, expressions become very hard to read.

              I usually claculate a set of flags - for example, Current_Month_Flag, YTD_Flag, Rolling12_Flag, etc... Each flag gets a 1 when the condition is true or null() when it's false.

              With the flags, you can construct your statement like this:

              "Current Month Sales" = sum(Sales*Current_Month_Flag)

              or, if you really want to use Set Analysis, the expression is still pretty simple:

              "Current Month Sales" = sum( {$ <Current_Month_Flag = {1} >} Sales)

              cheers,

              Oleg

               

                • Set Analysis - Has anyone got a tutorial/examples of this?
                  John Witherspoon

                  I wouldn't use sum(Sales*Current_Month_Flag). It might take the fewest characters to type, but it isn't particularly clear from a maintenance perspective, and isn't particularly fast from a performance perspective.

                  Marginally faster and (to me) much more clear is sum(if(Current_Month_Flag,Sales)).

                  The fastest but somewhat less clear (to me) approach is set analysis with the flag as mentioned, sum({<Current_Month_Flag={1}>}Sales). Since it is less clear, it may not be worth the headache if your data set isn't huge. But you may benefit from simply standardizing on the fastest approach, so that the same approach is used across all applications regardless of necessary performance in each.

              • Set Analysis - Has anyone got a tutorial/examples of this?
                Community Administrator

                Mike,

                Check out this file from the Share QlikViews section; might help out with some basic Set Analysis examples:

                http://community.qlik.com/media/p/64362.aspx