6 Replies Latest reply: Aug 31, 2014 4:01 AM by Jayanthan Sivaloganathan RSS

    Set Analysis and Yeartodate

      Hi All

       

      Could you please explain the meaning of Set Analysis given below

       

      sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} Sales )

       

      Further how can i change this to Yeartodate Calculation, further is there any pdf to learn about set analysis

       

      i would like to learn to calculate YTD current year as well as previous year

       

      engishfaque

      evan_kurowski

      jagan

       

       

      Best Regards

      Jayanthan

        • Re: Set Analysis and Yeartodate
          Prashant Sangle

          Hi,

           

          The basic YTD expression is

           

          Sum({<DateField={

           

          ">=$(=Date(YearStart(max(DateField)))<=$(=Date(max(DateField)))"

           

          }>}Sales)

           

          Regards

          • Re: Set Analysis and Yeartodate
            ISHFAQUE AHMED

            Dear Jayanthan,

             

            Set Analysis:

             

            It is a powerful qlikview feature and used for to compare different data sets. Set Analysis always enclosed with curly brackets {}.

             

            Example:

            1. Product sales of current year versus previous year.

             

            Set Analysis Expression consist on three things which are listed below:

            1. Set identifier

            2. Set operator

            3. Set modifier

             

            Further, I recommended you this book "Qlikview Scripting by Matt Floyd" for quick understanding.

             

             

            Kind regards,

            Ishfaque Ahmed

            • Re: Set Analysis and Yeartodate
              Evan Kurowski

              Jayanthan Sivaloganathan wrote:

               

              Hi All

               

              Could you please explain the meaning of Set Analysis given below

               

              sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} Sales )

               

              Further how can i change this to Yeartodate Calculation, further is there any pdf to learn about set analysis

               

              i would like to learn to calculate YTD current year as well as previous year

               

              Ishfaque Ahmed

              Evan Kurowski

              Jagan Mohan

               

               

              Best Regards

              Jayanthan

              Hello Jayanthan,

               

              It looks like you are performing a nested set-analysis expression summing the field [Sales] using a two field filter, [YEAR] and [MONTH].

               

              • You are grabbing the maximum YEAR out of all possible years.
              • You have nested expression which grabs all months less than or equal to the maximum MONTH that is found in the maximum selected YEAR.

               

              The granularity possible here is at the monthly level (meaning if you wanted to be able to jump to any calendar date and know what the YTD value for that given date was, you could not "split" a month using this method.  If the maximum date selected were the 15th of the current month and you wanted to compare against 1 month prior, you would only be able to compare 15 days from the current month against 28-31 days from the prior month).

              Oftentimes I get requests for set-analysis comparison to calculate matching time frames.  If I am at the 6th day of the current year, the request is to compare against only the first 6 days of the prior year (and not the entire month of the prior year), because comparing 6 days from January of this year against the full 31-days from the January of the prior year will not yield a true apples-to-apples comparison until the current month is fully complete and populated with 31 days of data.  So the question often is.. "where do we stand against how we were doing on this day of the year, last year". 

              To have the best granular control over your chronological set-analysis, form a date field that tags your fact information, and convert this date field to a pure integer field.  If the field only deals with Monthly level reporting, you can force each integer to either the first day or last day of each month via MonthStart() or MonthEnd() functions.

               

              Once you have a DATE_NUM field, all set analysis is then applied to an integer field and no other calendar fields need to be involved to pluck any range you like from the entire data span:

               

              DATENUM={">=$(Start)  <=$(End)" }

               

              Plugging in a variety of start or end points doesn't change the syntax of the base expression.  Key points in timeline are always the same and will float based on user-selection:

               

              Current Selected Date = Max(DATENUM)
              Current Year Start =  Num(YearStart(Max(DATENUM)))

              Prior Year Selected Date = Num(AddYears(Max(DATENUM)-1))

              Prior Year Start = Num(YearStart(AddYears(Max(DATENUM),-1)))


              Take for example a 60-day rolling calculation... it gets complicated when everything is packaged in full months only, and the year-turnover points start making plucking the right combinations of YEAR/MONTHs from the beginning of this year and the end of last year require additional logic, formulas, etc...  If you keep all set-analysis expressions centered around a DATENUM, they follow the same principle:

               

              DATENUM={"> MAX(DATENUM)-60 <= MAX(DATENUM)"}

               

              No matter where you reside in your result set, you should be able to get YTD from the selection point, and prior YTD of the same timeframe, down to the day level

               

              If at any point your application starts caring about time of day and timestamps, you're already set-up to do this without having to change set-analysis structure.  Allow the DATENUM to switch from an integer to a float by introducing timestamp fractions into the data, no need to add new set-analysis filtering fields.