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

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

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

Kind regards,

Rich

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

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

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.

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

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)