3 Replies Latest reply: Feb 17, 2011 12:57 PM by Martina Brenner RSS

    Evaluating the user selection - using {$<Year = {$(=min(Year))}>}

    Richard Sheppard

      Hi,

      In the past, I have found/used the following formula to return spend for the earliest Year that the user has selected:

      sum({$<Year = {$(=min(Year))}>} Euros/1000)

      can anyone tell me if the above can be adapted to evaluate a different field (ie CalYearMonth), or is the "{$<Year" function a built in one (and therefore cannot be modified), or other?

      I was hoping that the following would work, but alas, no:

      sum({$<CalYearMonth = {$(=min(CalYearMonth))}>} Euros/1000)

      (replacing the "Year" field, with "CalYearMonth")

      Please advise,

      Kind regards,

      Rich

       

        • Evaluating the user selection - using {$<Year = {$(=min(Year))}>}
          Neil Miller

          Yes, that should work fine, provided CalYearMonth is a sortable value. If it holds month names, but not their numeric equivilents, it would not be sortable.

          I'm guessing your problem is quotes:

          sum({$<CalYearMonth = {'$(=min(CalYearMonth))'}>} Euros/1000)
          String values need quotes around the Element Set.

          You should definitely read up on Set Analysis, it is one of the most powerful features of QlikView.

          • Evaluating the user selection - using {$<Year = {$(=min(Year))}>}
            Miguel Angel Baeyens de Arce

            Hello Rich,

            That's called "Set Analysis" and you can use any field and compare to any value, variable, other fields, etc.

            General syntax is

             

            Sum({< CompanyID = {"A*"} >} Euros)


            The left (bold) part is always a field. The right part can be a variable, an expression that returns a value or a list of values accordingly formatted (quoted when literals, double quoted when used wildcards as in the example, date format, etc.) It can be of course a list of values likewise.

            So if in your example

             

            Min(CalYearMonth)


            returns a possible value for CalYearMonth (very likely) then your expression will work.

            Hope that helps.

            • AW:Evaluating the user selection - using {$<Year = {$(=min(Year))}>}
              Martina Brenner

              Hi,

              you are right but you have to think at the datefunction Month, its a dual one, that means you have a text and an numeric part. If you use the num-function, it will work:

              1. enter in the script the new field Num(Month(datefield)) As NumMonth

              2. enter a variable like this: vNumMonth =Num(CalMonth)

              3. use set analysis lik this: Sum({<Year={$(=Min(Year))},NumMonth={$(vNumMonth)}>} Euros/1000)