Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
String values need quotes around the Element Set.sum({$<CalYearMonth = {'$(=min(CalYearMonth))'}>} Euros/1000)
You should definitely read up on Set Analysis, it is one of the most powerful features of QlikView.
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:
String values need quotes around the Element Set.sum({$<CalYearMonth = {'$(=min(CalYearMonth))'}>} Euros/1000)
You should definitely read up on Set Analysis, it is one of the most powerful features of QlikView.
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.
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)