SET ANALYSIS in Qlikview Part3 (YTD , MTD , Rolling 12 months)

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.