Using a
function
So far, we have selected
members that we knew in advance they will be or not in the set. But we can also
use functions that will
return members according to a test, a rank ….
4.6.1 Numeric function
Syntax:
<Dimension = {" $(=f(args)) "}
the
$ sign is used to expand or interpret the function
you want to get the last
year of the application, use the function max([{set}] FieldName)
<Year = {"$(=max({1} Year))"}>
Please note:
- The max function, like any aggregation function, take into account
the current selection
- We have used an inner set. {1} inside the max function
means the whole application. If we do not
use it, the max function
will return the last year among those chosen by the user
- We use two equal signs, 1 before the curly braces, 1
between the quotes
If you are looking for the
previous year of the last year of the selection, you may write one of the two
syntaxes that are
equivalent:
- <Year = {"$(=max({$} Year)-1)"}>
- <Year = {"$(=max(Year)-1)"}> , {$} = current selection, it is the default
Most of the time, the set
analysis become tedious, especially when writing YTD, Moving Total,
Comparisons vs Year ago.
One way to simplify the syntax is to populate some fields directly into the
model.
Herebelow, the field A-1
stores the key for the Yr Ago period, P-1 stores the key for the previous
period,
YTD stores a flag (1 or 0)
to indicate if the period should be taken to compute the YTD data:
If you want to get the
periods of the Year Ago, you just need to use the appropriate field and the concat
function:
sum({<MONTH=, YEAR=, TIME_KEY = {$(=concat([A-1], ',')) } >} VALEUR)
YTD:
sum({<MONTH=, YTD = {1}, YEAR = >} VALEUR)
YTD from Year Ago:
sum({<MONTH=, YTD = {1}, YEAR = {$(=YEAR-1)} >} VALEUR)
MOVING TOTAL 12 periods:
sum({<MONTH=, YEAR=, TIME_KEY={">$(=Max(TIME_KEY)-12)
<=$(=Max(TIME_KEY))"}>} VALEUR)
MOVING TOTAL 12 periods,
Year Ago:
sum({<MONTH=, YEAR=, TIME_KEY={">$(=Max(TIME_KEY)-24) <=$(=Max(TIME_KEY)-12)"}>} VALEUR)
Function returning
members
The text function is
between quotes. No $ sign.
Syntax:
<Dimension = {"=f(parameters) "} >
We want to aggregate the
volume sales for the MANUFACTURERs whose value sales is greater than
100,000 dollars based on
the current selection:
sum( {$ <MANUFACTURER_LDESC={"=sum([Value
Sales])>100000"} >}
[Volume Sales])
But we can also decide to
search the MANUFACTURERs on a specific period, or for specific products. We
will create an inner set:
sum( {$ <MANUFACTURER_LDESC={"=sum({1<TIME_SDESC={'P 01/13'},
CATEGORY_LDESC={'ACC','CHEESE CAKE’ }>} [Volume Sales])>100000"} >} [Volume Sales])
Remember that the members
could be enclosed between single or double quotes. Because the function
uses double quotes, we
will use for the members either the single quotes or the square brackets [].
I want to find the
Manufacturers whose sales are over 100,000 for the 2 categories CHEESE CAKE and
ACC in January 2013
(period P01/13). But I want to remove from that list those whose sales are
lower than
50,000 for all categories
in January 2012:
I need two sets : {<set
1> - <set 2>}, each of them will use the function sum():
Set 1 = 1 <MANUFACTURER_LDESC={"=sum({1< TIME_SDESC={'P
01/13'},CATEGORY_LDESC={'ACC','CHEESE CAKE'}>} [Value
Sales])>50000"} >
Set 2 = <MANUFACTURER_LDESC={"=sum({1< TIME_SDESC={'P
01/12'},CATEGORY_LDESC={'*'}>}
[Value Sales])>100000"} >}
In a global syntax :
sum( {1 <MANUFACTURER_LDESC={"=sum({1< TIME_SDESC={'P
01/13'},CATEGORY_LDESC={'ACC','CHEESE CAKE'}>} [Value
Sales])>50000"} >
- <MANUFACTURER_LDESC={"=sum({1< TIME_SDESC={'P
01/12'},CATEGORY_LDESC={'*'}>} [Value
Sales])<50000"} >}
[Value Sales])
this could be reduced a
little :
sum( {1 <MANUFACTURER_LDESC={"=sum({1< TIME_SDESC={'P01/13'},CATEGORY_LDESC={'ACC',
'CHEESE CAKE'}>} [Value Sales])>50000"} -{"=sum({1< TIME_SDESC={'P01/12'},CATEGORY_LDESC={'*'}>}
[Value Sales])<50000"} >}
[Value Sales])
Please note that the
function is between quotes: you will have no help to write the syntax, for the
function,
nor for the fields.
QlikView is case sensitive for the field names: if you write TimeKey instead of
TimeKEY,
you will get 0. So test
the function before including it in the set analysis.
If you want to get the Top
20 products:
Sum({<Product = {"=rank(sum(Sales), 4)<= 20"}>} Sales)
And now the Top 20
products of the brand 10 (we need to use an inner set):
Sum({<Product = {"=rank(sum({<Brand =
{10}>} Sales), 4)<= 20"}>} Sales)
Take care NOT to write:
Sum({<Brand = {10}, Product = {"=rank(sum(Sales), 4)<= 20"}>} Sales)
You would get the
intersection of Brand 10 and the top 20 products. And perhaps, you will get
nothing
because there is no
product of the brand 10 among the top 20 products.
Keep also in mind that the
set analysis is “computed” once, before the chart is computed. Not
once
per row: you will not get different products by region if you have a region
dimension in your chart.
