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

[#1]

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

[#2 · Accepted solution]

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.

[Miguel_Angel_Baeyens]

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.

[brenner_martina]

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)