How To / Missing Manual

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Below() and Above()

Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure.).

On the first row of a column segment a NULL value will be returned, as there is no row above this one.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column........

• Returns value from  previous (Above) ornext (Below) row(s)
• Returns NULL for the first or last row.
• Equivalent to before()and after()for columns in Pivot Table.

Syntax:

TOTAL[<fld{,fld}>]]expr - expression

offset - if greater that 0 will move the evaluation of expression to rows

further down or above

count - this parameter will only works with Chart Range Function (like RangeSum), it will specify the numbers of rows to be taken for calculations.(Please see last example)

Data Model:

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

Year ,Month ,Sales

2015 ,January, 10

2015, February,20

2015 ,March ,30

2014 ,January ,10

2014 ,February,20

2014 ,March ,30

2013 ,January ,10

2013 ,February ,20

2013 ,March,30

]

Example 1:

Let's build a Straight Table with Year and Month as dimensions and expressions as below:

The left hand side shows use of sum(Sales) the right hand side result from our new expression.

                sum( Sales )    -----------> above(sum( Sales ) )

In each groups the first value is now NULL,the last values(30) have been omitted and the rest of the rows have been assigned value from one row below current row.

                sum( Sales )    ----------->  below(sum( Sales ) )

In each groups the last value is now NULL,the first values (10) in each group have been omitted and the rest of the rows have been assigned value from one row below current row.

Example 2

By specifying the second criteria as 2 ,values in each group are shifted two rows up or down

          sum( Sales )-----------> below sum( Sales ), 2 )

          sum( Sales )-----------> above( sum( Sales ), 2 )

Example 3

        above(TOTAL sum( Sales ) )      below(TOTAL sum( Sales ) )

Adding TOTAL before Sum will result with the first or last value to be omitted and the calculation to be shifted one row down or up.

Example 4

rangeavg (Above(sum(Sales),1,2))

rangeavg (Below(sum(Sales),1,2))

RangeAvg() takes 3 parameters

-expression ---> Above/Below(sum(Sales),

-offset of rows--->1

-number of rows to sum--->2

                                                   sum(Sales)                         rangeavg (Above(sum(Sales),1,2))

For each group in right table:

• First row is NULL
• Rows 2 and 3 are averages of values from two rows above current one (first we are going 1 row above and then adding 2 rows)

      sum(Sales)                          rangeavg (Below(sum(Sales),1,2))

For each group in right table:

• Rows 1 and 2 are averages of values from two rows below current one (first we are going 1 row above and then adding 2 rows)
• Last row is NULL

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Missing Manual - Below() and Above()

How to use- Dual()

Missing Manual - Before() and After()

How to use - MissingCount()

What it is used for?

The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a prefix.’

In situation where we have two (or more) Tables with the same field names:

Product	Payment

The Qualify statement will assign name of the Table to fields:

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [Qualify.xlsx]

QUALIFY *;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY *;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

The Outcome:

Table Viewer:

Read only if you need to know more....

If we have not used ‘QUALIFY’ statement in situation as above Qlikview would map both of the Tables with outcome like below:

The end result  -merge of those two Tables is correct only for the “Serial No” fields

The “Value” and “Category” fields although merged is none of use to us.

To fix this we can only Qualify fields that we do not want to associated:

QUALIFY Category,
Value;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY Category,
Value;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

or by using UNQUALIFY statement:

(which works as opposite to QUALIFY)

QUALIFY *;
UNQUALIFY [Serial No];
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY *;
UNQUALIFY [Serial No];

Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

In second example the ‘QUALIFY *’ part will add Table name to all fields and the UNQUALIFY statement will remove those names only from specified fields(this method is very useful if we got large numbers of fields)

Outcome is as below:

Fields:

Table Viewer:

Feeling Qlikngry?

How To /Missing Manual(18 articles)

This example QlikView document shows how to create a bar chart that shows variance to target, both as an absolute value and as a percentage.

The charts produced look like this:

By showing both these charts you can see both how regions are comparing to each other and against the targets that have been set for them.

The example was created to accompany the Quick Intelligence blog post, that can be read here:

https://www.quickintelligence.co.uk/qlik-target-bar-chart/

The example is also available on Qlik Community as a Qlik Sense application.

A list of all our downloadable example files can be found here:

https://www.quickintelligence.co.uk/examples/

Hope you find this application useful.

Regards,

Steve

https://www.quickintelligence.co.uk/

Definition:

Returns the number of dimension columns that have non-aggregation content. i.e. do not contain partial sums or collapsed aggregates.

A typical use is in attribute expressions, when you want to apply different cell formatting depending on aggregation level of data.

This function is only available in charts. For all chart types except pivot table it will return the number of dimensions in all rows except the total, which will be 0.

What does it mean?

We have Table with 4 dimensions(columns): Product,Category,Type,Sales

Now we want to create Pivot Table by using those Dimensions.

We are going to use only 3 of them(Product,Category,Type) and use 4th(Sales) in our expression.

The result is shown below:

This Pivot Table  has 3 dimensions so its maximum dimensionality is 3.

For better understating please see  table below.

The function is used to show on which dimensionality level each of the Pivot Table row is:

'Sugar' has dimensionality of 1 which is Total for that 'Product'.

'Salt' has dimensionality of 2 which is Total for each 'Category' of that 'Product'.

'Oil' has dimensionality of 3 which is single value for each 'Type' of the 'Product's' 'Category'.

So then more Dimension we use the greater dimensionality of our Pivot Table is.

Practical use:

1) To show the level of dimensionality:

Expression:

if(Dimensionality()=1 ,RGB(151,255,255),if(Dimensionality()=2 ,RGB(0,238,0),if(Dimensionality()=3,RGB(255,130,171))))

2) Highlight background of rows which on each level  fall into certain condition:

Expression:

if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))

LEVEL1 --> Values <140	LEVEL 2 --> Values <=20	LEVEL 3 --> Values <=20

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [Dimensionality.xlsx]

Directory;

LOAD Product,

     Category,

     Type,

     Sales

FROM

[Dimensionality.xlsx]

(ooxml, embedded labels, table is Sheet1);

Felling Qlingry?

How To /Missing Manual (25 articles)

Have you ever wonder how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Dual()

Definition:

Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. The dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.

Syntax:

where

text - this is first column of data

number- this is second column of data

Qlikview has specific way of storing data. Each field is represented by Text and Number data type called dual.

The Text format is visible to user and the Number is being used for calculation and sorting.

                    You can imagine that each field has top Text layer and bottom

                    (like salad in your Hamburger) Number layer.

This association works most of the time and for most of the data type but there are situation that we need to force this association to work the way we want.

Preparation

Remember

• To use the script below the csv file must be saved in the same folder that your qvd file

Qlik help  example contains two tables: inline and from csv file(sample attached)

load dual ( string,numrep ) as DayOfWeek inline

[ string,numrep

Monday,0

Tuesday,1

Wednesday,2

Thursday,3

Friday,4

Saturday,5

Sunday,6 ];

Directory;

LOAD Date,

    Sales

FROM

afile.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//The last line  is not part of Qlikview help but you have to have this line to correctly load data into Qlikview

after Reload we have:

As you probably know when Values are on the left hand side they represent Text when on the right Number.

So why we have Text on the right?

In the background (during load) the numbers have been associated  with the text.

So what you see is the bun what you do not is the underlay salad:

Practical use:

Example 1

The simple but maybe not the most useful calculation is to add Days of the week from our data =sum(DayOfWeek)

Example 2

Counting days of the week:

Example 3

(please use attached xlsx file and below Script)

A survey has been conducted and the data in Excel file looks like this:

What we want is to assign more meaningful description to the rate numbers.

Script:

Data:

mapping load * Inline

[ Rate,Value

Very Good,5

Good,4

Average,3

Bad,2

Very Bad,1

]

;

Sort:

load

Responds,

dual(Rate,ApplyMap('Data',Rate)) as Rate

FROM

Survey.xlsx

(ooxml, embedded labels, table is Sheet1);

Now we can create Straight Table or bar Bar Chart:

This function can be use in many different ways.Please research our community to find more examples.

Still feeling hungry?

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Not authorized to view the specified document 8150

How to use- Dual()

What it is used for?

To return values from another table field based on matching field value from current table.

Typically used when only one field records from another table are needed.

(You are dropping this (second) table later on.(see below) )

How does it look like?

How is it design?

• dual - this is not actual part of the function but  prefix used to indicate what kind of value this function returns –in this case it means that this function can return numbers and characters
• Lookup - function name

and four arguments:

• field_name – is the name of a field in the other table – in the table where we want the value to take from
• match_field_name- is the name of a field in the other table – this is the matching value to the next argument

                                                                        <---->

• match_field_value - is the name of field in the this table-matching with previous argument
• [, table_name] - This is name of the other table (this argument is optional if omitted current table is used)

Preparation:

We have got two tables in Excel file.

For convenience let’s call them ‘Table1’ and ‘Table2’:

Table1	Table2

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [LookUp.xlsx]

Table2:
LOAD  Category,
Sales,
Country
FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table2);

Table1:
LOAD Category,
Lookup('Country','Category',Category,'Table2') as Country

FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table1);
drop table Table2;

Remember  to... Look Up!

(This is where the name of this function has another meaning)

Table 2 must be above Table1!

or after Reload you will get script error.

How does it work?

Formula:

Script:

Table2:
LOAD  Category,
Sales,
Country
FROM
[Lookup.xls]
(ooxml, embedded labels, table is Table2);

Table1:
LOAD Category,
Lookup('Country','Category',Category,'Table2') as Country

FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table1);
drop table Table2;

Remember:

• First two arguments and the last one must be enclosed in apostrophes '' .
• You need to drop Table2 using: drop table statement or all fields from Table1 will be loaded, bringing unexpected results.

The final tables you should see are like this:

Pros:

• You can return any field from second table based on any matched field from first table -

You can look at first field in the table to return third one and to look at third field to return the first one.

Cons:

• Little bit confusing with the syntax
• Slower than Apply Map
• No ‘False’ return – If there is no match a Null is returned.

      To overcome this drawback we can wrap the formula in IF function:

    If(isnull(Lookup('Country','Category',Category,'Table2')),'No                      match',Lookup('Country','Category',Category,'Table2')) As Country

Exercise :

Return field ‘Sales’ from Table2 by using ‘Category’ field from Table1.

(Answer at the end of this article)

Appendix:

For many of you coming from Excel world and working with VLOOKUP this syntax is little difficult to understand at first.

Why we can not have:

Take value from this table go to the another table matched the field and return what we need?

So the syntax would have been:

Lookup(Category,Table2,Category,Country)

Would this not be simpler?

This is open question so please state your case....

Answer to Exercise:

Lookup('Sales','Category',Category,'Table2') as Sales

Still feeling hungry?

How To /Missing Manual(17 articles)

Bullet points:

• MaxString and MinString belongs to Qlikview String Aggregation function.
• They are equivalent to MAX and MIN for integers
• They return the highest and lowest string value

What actually a value of a string?

This is the value described by ANSI character standard where 0 ( NULL) is equal  to 0 and ÿ to 255.

(For full ANSI character set please attached dosument)

Taking only the standard English alphabet (A-z)  ‘A’ will be the MIN and ‘z’ will be the MAX.

Remember:

In ANSI standard A < a and Z < z

(A=65,Z=90,a=97,z=122)

Language specific characters will be not recognized in the place where they are in your alphabet but they will be 'pushed' to the end

(see ANSI table)

MinString and MaxString works on dimensions (columns) only so you cannot use is to evaluate single character or strings .

(MaxString(‘a’,’z’…) is not supported

Examples:

1)    MIN & MAX (textbox)

Data	Expression	Results
	MinString(Category) MaxString(Category)

2) MIN and MAX of String per group (Straight table)

Data	Expression	Result
	MinString(Category) MaxString(Category)

3) With condition:

Return Max and Min string based on another column

We are looking for MIN and MAX string value from column Category where Country='Poland'

Data	Expression	Solution	Result
	MinString( if(Country='Poland', Category, null()) ) MaxString( if(Country='Poland', Category, null()) )

4) With LEFT/RIGHT

When used with LEFT the result will be the same as without as the value will be still based on first characters.

Data	Expression	Solution	Result
	=MinString( Right(Country,1)) =MaxString( Right(Country,1))

5) With MID

6) With Substring

When our data are little scrambled -TextBetween will return string between '.' and end of the row.

Data	Expression	Result
	=MinString(TextBetween(Country,'.','',1)) =MaxString(TextBetween(Country,'.','',1))

7) In set analysis

When Criteria of Sales is 2 return Country with highest and lowest string Value

😎 With Aggr()

For each Country return lowest and highest Category string value.

If know about other ways of using these functions please let me know.

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Before () and After()

Description

Returns the value of expression evaluated with a pivot table's dimension values as they appear in the column before the current column within a row segment in the pivot table. This function returns NULL in all chart types except pivot tables.

On the first column of a row segment a NULL value will be returned, as there is no column before this one.

If the table is one-dimensional or if the expression is preceded by the total qualifier, the current row segment is always equal to the entire row.

If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.

Specifying an offset greater than 1 lets you move the evaluation of expression to columns further to the left of the current column. A negative offset number will actually make the before function equivalent to a after function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current column. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table columns counting to the left from the original cell. In this form, the function can be used as an argument to any of the special Chart Range Functions.

• Returns value from preceding (Before)or following (After) column
• Returns values only in Pivot Table otherwise NULL.
• Works only with Horizontal dimension.
• Equivalent to below() and above() for vertical Pivot Table dimensions.

Data Model:

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

Product ,Category ,Sales

Salt ,White, 30

Salt, Brown ,40

Salt ,Red ,30

Sugar ,White ,10

Sugar ,Brown ,20

Sugar ,Red ,20

Wine ,White ,40

Wine ,Brown ,30

Wine ,Red ,10

]

Example 1:

Let's build a Pivot Tables with Product as dimension and expressions as below:

The left hand side shows an ordinary use of Sales the right hand side result from our new expression.

                sum( Sales )    ----------->  before(sum( Sales ) )

Salt value has been assigned to Sugar,

Sugar to Wine,

Salt  is now NULL .(there is no column before this one)

                sum( Sales )    -----------> after(sum( Sales ) )

Here we see the opposite result:

Sugar value has been assigned to Salt,

Wine to Sugar

and Wine is  NULL (no column after this one)

Example 2

Specifying the second criteria as 2 we shifted all values by 2 columns right or left.

                                before( sum( Sales ), 2 )

                                after( sum( Sales ), 2 )

Example 3

before(total sum( Sales ) )

after(total sum( Sales ) )

When using with one dimension this expression returns values as if we have used ordinary  before/after (Sum(Sales))

For more than one dimension the return value will be based on last column of the first(top) dimension and then appropriate columns for the second dimension.

Salt and Sugar have been omitted leaving Wine as the last column

Example 4

rangesum (Before(sum(Sales),1,2))

RangeSum() takes 3 parameters

-expression ---> Before/After(sum(Sales),

-offset of columns--->1

-number of columns to sum--->2

                                                                  sum(Sales)                            rangesum (Before(sum(Sales),1,2))

In our example we are shifting our calculation one column to the right:

-Salt -  As there is no column before Salt this column has been ignored

-Sugar -we are shifting our calculation one column to the left (Salt)  and are summing previous two columns.

As there is no column before Salt the final result is value of Salt alone.

-Wine- by shifting calculations one column to the left(Sugar) and summing two previous columns we getting 100+50=150

                    sum(Sales)                            rangesum (after(sum(Sales),1,2)

In this example we are shifting our calculation one column to the right:

-Wine -  As there is no column after Salt this column has been ignored

-Sugar -we are shifting our calculation one column to the right(Wine) and aresumming previous two columns.

As there is no column after Wine the final result is value of Wine alone.

-Salt- by shifting calculations one column to the right(Sugar) and summing two previous columns we getting 50+80=130

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

    Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Map...using

or some kind of a lookup...

In most of the cases we want to keep current field and by mapping corresponding values create new Field.

(Don't join - use Applymap instead)

But sometimes we want to replace data with new one.

Here is where map...using comes in handy..

Description(Qlikview Help)

The map ... using statement is used for mapping a certain field value or expression to the values of a specific mapping table. The mapping table is created through the Mapping statement.

The automatic mapping is done for fields loaded after the map ... using statement until the end of the script or until an unmap statement is encountered.

The mapping is done last in the chain of events leading up to the field being stored in the internal table in QlikView. This means that mapping is not done every time a field name is encountered as part of an expression, but rather when the value is stored under the field name in the internal table. If mapping on the expression level is required, the Applymap() function has to be used instead.

Syntax:

*Fieldlist -  is name of the field which value we will be look for

mapname - us the table from where the data will be picked

Replacing matching fields values with corresponding values from the Mapping Table

• Mapping Table must have only two columns
• The fields/tables to be replaced (or map) must be loaded after the map..using statement

Data Model (Example 1)

The outcome is as below:

PL - have been replaced by Poland

DE - have been replaced by Germany

FR and GB - does not have a match in Cmap table

Data Model (Example 2 & 3)

X:

MAPPING LOAD * INLINE [

Code,Country

PL,Poland

UK,United Kindgdom

DE,Germany

];

map A, B, C using X;

Data:

LOAD * INLINE [

A,  B  ,C

PL, GB,DE

GB, PL,FR

DE, FR,PL

FR, DE,GB

];

Outcome:

In this example matching values from all three fields have been replaced by corresponding values.

The A,B,C field names can replaced by * (Example 3) to get the same outcome

map * using X;

If you wonder where this statement may be useful , think of a model with more than one table where values have to be replaced

In below example all fields that have been stated as map criteria (A,B,C,G - no matter which table ) have been replaced by corresponding values:

Header 1	Header 2
X: MAPPING LOAD * INLINE [ Code,Country PL,Poland UK,United Kindgdom DE,Germany ]; map A,B,C,G  using X; Data: LOAD * INLINE [ A,  B  ,C PL, GB,DE GB, PL,FR DE, FR,PL FR, DE,GB ]; Data2: LOAD * INLINE [ E,  F  ,G PL, GB,DE GB, PL,FR DE, FR,PL FR, DE,GB ];

Feeling Qlikngry?

How To /Missing Manual(20+ articles)

    Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Valueloop() & VaueList()

Both of those functions belong to Synthetic Dimension Functions.

Synthetic Dimension is a type of Calculated Dimension

- the difference between "standard" Calculated Dimension and Synthetic one is that

the standard dimensions are based on values from existing fields

whereas for Synthetic Dimensions those values are created "on the fly".

The drawback here is that you can not mix standard dimensions with synthetic in the way you would expect.

(please see "Practical use").

ValueLoop()

Used:

Back End -No

Front End - Yes

Description(Qlikview Help)

Returns a set of iterated values which, when used in a calculated dimension, will form a synthetic dimension.

The values generated will start with the from value and end with the to value including intermediate values in increments of step. In charts with a synthetic dimension created with the valueloop function it is possible to reference the dimension value corresponding to a specific expression cell by restating the valuloop function with the same parameters in the chart expression. The function may of course be used anywhere in the layout, but apart from when used for synthetic dimensions it will only be meaningful inside an aggregation function

Create series of numbers in a range given by criteria.

from - first value

to - last value

step - intermediate values criteria.

When step is missing 1 is assumed

Qlikview help examples are very straightforwards and easy to understand:

Example 1	Example 2	Example 3
valueloop ( 1, 3 ) From 1 to 3, (step is omitted so 1 is assumed):	valueloop ( 1, 5, 2 ) From 1 to 5, step 2: 1,1+2=3,3+2=5	valueloop ( 11 ) returns the value 11

Practical use

Example 1

If you need to provide calculations to check if the MOD of values from 0 to 100 with step 5 is divided by 10 without remainder

Create Dimension: ValueLoop(0,100,5)

and Expression: if(mod(ValueLoop(0,100,5),10)=0,'OK', 'No OK')

See also:

How to create a Square Pie Chart

or

qlikfreak.wordpress.com/2014/06/17/infographics-in-qlikview-vol-2/

ValueList()

Used:

Back End -No

Front End - Yes

Description(Qlikview Help)

Returns a set of listed values which, when used in a calculated dimension,

will form a synthetic dimension. In charts with a synthetic dimension created with the valuelist function it is possible to reference the dimension value corresponding to a specific expression cell by restating the valuelist function with the same parameters in the chart expression. The function may of course be used anywhere in the layout, but apart from when used for synthetic dimensions it will only be meaningful inside an aggregation function

Create series of values from given list

v1 - list of values

Again, both of those examples are very easy to understand:

Header 1	Header 2
valuelist ( 1, 10, 100 )

Header 1	Header 2
valuelist ( 'a', 'xyz', 55 )

Practical use

Data Model

LOAD Date,

Year(Date) as Year,

Values

inline [

Date, Values

01/01/2009, 1

11/04/2009, 2

20/07/2009, 2

28/10/2009, 2

05/02/2010, 2

16/05/2010, 2

24/08/2010, 1

02/12/2010, 1

]

The usual way of creating straight table is be to add Year as Dimension and sum(Values) as expression.

This will return value for each Year.

But if we want o use Synthetic Dimension in the same way this will return only one Total value for both years.

One of the way to use this function is to create list (similar to Statistics Box) with your own KPI's:

and then use nested IF statement to create your Metrics

=if(ValueList('Sum','Count','Average')='Sum',Sum(Values),

if(ValueList('Sum','Count','Average')='Count',Count(Values),Avg(Values)))

to get below result:

As nesting IF's can be tricky and cumbersome beyond 2 or 3 criteria we can use Pick/Match functions to improve our calculations:

=pick(match(ValueList('Sum','Count','Average'),'Sum','Count','Average')

,Sum(Values),Count(Values),Avg(Values))

Conclusion:

Although both of those function are not very often used

(they did not make to final 30 of  rwunderlich survey

Help with QV Function Survey‌   

you can find a practical way of using them.

Feeling Qlikngry?

How To /Missing Manual(19 articles)

    Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

FieldValue()

This function belongs to Inter Record Functios and has few rules:

• Sorting on y-values in charts or sorting by expression columns in straight tables is not allowed when Chart Inter Record functions are used in any of the chart's expressions. These sort alternatives are therefore automatically disabled.
• Suppression of zero values is automatically disabled when this functions is used

Data Model

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

]

Now create Straight table and  remember to sort dimension and expression by load order

Returns values from position specified by second criteria (elem_no)

• Works only with distinct value
• field_name must be given as a string value (enclosed with single quotes ('))
• If elem_no is larger than the number of field values, NULL is returned.

Syntax:

Field_name -  is a name of of your field(column)

elem_no - position from the top

Example

fieldvalue( 'Helptext', 5 )

Bonus

By using:

=FieldValue('Name', RowNo()+1)

=FieldValue('Name', RowNo()-1)

you can shift values one row below/or above current one loosing the first or last value.

QlikView creates a number of log files and XML files.  From project folders through to reload logs and QMC schedules.  Perhaps unsurprisingly the best tool to consume these files is QlikView itself.

This application loads the structure files that QlikView allows you to extract based on the data model of the currently open app.  It then gives a few simple ways of viewing that information.

There is an accompanying blog post describing the ways that you can use the structure files created by QlikView, and apps such as this to quickly get a handle on a new data source and work out a plan of attack to analyse it.

This can be found here:

http://www.quickintelligence.co.uk/qlikview-data-structures/

There is also a list of the other apps that I have made available on Qlik Community on the site, here:

http://www.quickintelligence.co.uk/qlikview-examples/

If you have any questions about the application, or suggestions on how it could be improved, please leave these below or in the comments on the blog post itself.

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

    Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Peek()

not a boo...

This function belongs to to Inter-Record functions and can only be used in the script (back-end).

Description(Qlikview Help)

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label  without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Syntax:

Field_name -  is a name of of your field(column)

row_no - the row from which the data is returned

(0 is first row

1 is second row

and so on..

-1 is the last row)

table_name - a name of table from where the data are fetched

Returns values from previous row or row specified by the row-no argument.

• If no row is stated, -1 is assumed.
• you can reference field that was not been previously loaded (created on the with AS alias)(See "Bonus 2" example)
• From the Help description "Fieldname must be given as a string (e.g. a quoted literal)."

          This is true only if we use this function to create variable (please see below examples)

Data Model
(Copy and Pasted below code into Edit Script window and reload)

Tab1:

load

peek(Sales) as S1,

peek( Sales,2 ) as S2,

peek( Sales,-2 ) as S3,

if(rowno()=1,Sales,Peek(RunnigTotal,-1)+Sales) as RunnigTotal,

Previous(Sales) as PSales,

numsum( Sales, peek( 'Sales' ) ) as Bsum,

Sales

inline [

Sales

100

200

300

400

]

;

load

peek( Sales, 0, 'Tab1' )as S4

resident Tab1;

LET vpeek= peek( 'Sales', -1, 'Tab1' ) ;

Please see below swuehl comments about difference when you use negative or positive numbers as second argument.

Is worth to mention that Peek() is reading from Output table(opposite to Previous() function which reads from Input Table.

Example:

OutputTable:

LOAD field

RESIDENT InputTable;

source:Difference between peek() and previous() funcation‌‌‌

Example 1

In this example as we did not specify the row_no argument, all but last rows are returned.

                                  peek( 'Sales' )

                                                  peek( 'Sales' ) = peek( 'Sales',-1)

Example 2

IF the row_no argument  is added only ONE value is returned

                        peek( 'Sales',2 )  Remember 2 = third row

Example 3

If we use negatives numbers as row_no  the return value is our initial table minus number of rows specified by the second argument.

                              peek( 'Sales',-2 )

As you see the last 2 rows have been removed from the table.

Example 4

The below syntax is used when we want to return value from external table(see data model)

peek( 'Sales', 0, 'Tab1' ) as S4

Example 5

Each row is a SUM of current row + one row above.

numsum( Sales, peek( 'Sales' ) ) as Bsum

Bonus

How to store value into variable:

With below example

LET vpeek= peek( 'Sales', -1, 'Tab1' ) ;

we will store value of 400  in variable vpeek

and then use in front end development.

Bonus 2

As previously stated we can use Peek to return values from field that was not been yet created.

How does it wok?

In our data model we have this line:

if(rowno()=1,Sales,Peek(RunnigTotal,-1)+Sales) as RunnigTotal,

Although RuningTotal has not yet been loaded we can return the values from that line:

Still feeling Qlikngry?

How To /Missing Manual(17 articles)

Prologue...

Firstly I would like to say that probably it would be easier to call this function Horizontal Dimensionality (or HDim or even SecDim)

This could clear some clouds - when you first see this function you may feel that this another level for dimensionality function.

(for those who wants to know more about the dimensionality function please see this document

How to use - Dimensionality()

What it is used for?

Returns the number of dimension pivot table rows that have non-aggregation content. i.e. do not contain partial sums or collapsed aggregates. This function is the equivalent of the dimensionality function for horizontal pivot table dimensions.

The secondarydimensionality() function always returns 0 when used outside of pivot tables.

What does it mean?

Where dimensionality shows number of vertical dimensions, secondarydimensionality shows  number of horizontal dimensions.

Lets take our sample data.

We have four dimensions(columns):

Product,Category,Type and Sales.

Each Product have 2 Categories

Each Category can have up to 3 Types

Let's create Pivot Table using 3 of those Dimension Product,Category and Type and use  Sales as our Expression.

This Pivot Table  has 3 dimensions so its maximum dimensionality is 3.

'Sugar' has dimensionality of 1 which is Total for that 'Product'.

'Salt' has dimensionality of 2 which is Total for each 'Category' of that 'Product'.

'Oil' has dimensionality of 3 which is single value for each 'Type' of the 'Product's' 'Category'

So in few words : secondarydimensionality (and dimensionality) is the number of dimensions used in a Table

For better understating please see  table below:

The function is used to show on which dimensionality level each of the Pivot Table row is:

Practical use:

1) To show the level of dimensionality:

Expression:

if(secondaryDimensionality()=1,RGB(151,255,255),if(secondaryDimensionality()=2,RGB(0,238,0),

if(secondaryDimensionality()=3,RGB(255,130,171))))

2) Highlight background of rows which on each level  fall into certain condition:

Expression:

if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))

LEVEL 1 --> Values <35	LEVEL 2 --> Values <=15	LEVEL 3 --> Values <=20

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - secondarydimensionality.xlsx

Directory;

LOAD Product,

     Category,

     Type,

     Sales

FROM

secondarydimensionality.xlsx

(ooxml, embedded labels, table is Sheet1);

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Not authorized to view the specified document 8150

How to use- Dual()

Expression Scene Investigation, E1: Moving Average

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

GetFieldSelections()

Definition:

Returns a string with the current selections in a field.

valueSep - is the separator to be put between field values. The default is ', '.

max_values  - is the maximum number of field values to be individually listed.

                      When a larger number of values is selected the format 'x of y values' will be used instead.

                      The default is 6.

Preparation:

Script to play with:

Load * Inline

[

Year

2007

2008

2009

2010

2011

2012

2012

2013

2014

2015

2016

]

Example 1:

GetFieldSelections(Year )

Our field Year has 10 values (Years from 2007 to 2016)

The default separator is ',' (coma)

The maximum default numbers of values is 6

We used the same expression for all 3 sets of data

Left: Selection of 6 values

Middle: When more that 6 values the result will change to show the values which are not selected.

Right: Selecting whole sets of values will return 'ALL' as result

Data	Expression and Results

Example 2: valueSep

We can assign here any character (or string).

Examples 3: max_values

The default values of 6 is overwritten by the value we have specified(10)

Interesting:

When playing with this function somehow I managed to get result like below

If you can replicate it please post it.

Bonus:

Practical use:

PROBLEM OF DIFFERENCE OF SUM

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

How to use - MissingCount()

How to use- Dual()

Expression Scene Investigation, E1: Moving Average

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Top() and Bottom()

Description

Returns the value of expression evaluated with the chart's dimension values as they appear on the last row of the current column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column.

If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order. The inter field sort order for pivot tables is defined simply by the order of the dimensions from left to right. For other chart types this can be manipulated in the Chart Properties: Sort dialog. ...

• Returns value from  first (Top) or last (Bottom) row(s) per each group

Syntax:

TOTAL[<fld{,fld}>]]expr - expression

offset - if greater that 0 will move the evaluation of expression to rows further down or above

count - this parameter will only works with Chart Range Function (like RangeSum), it will specify the numbers of rows to be taken for calculations.(Please see last example)

Data Model:

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

Year ,Month ,Sales

2015 ,January, 10

2015, February,20

2015 ,March ,30

2014 ,January ,40

2014 ,February,50

2014 ,March ,60

2013 ,January ,70

2013 ,February ,80

2013 ,March,90

]

Let's build a Straight Table with Year and Month as dimensions and expressions as below:

Example 1:

The left hand side shows use of sum(Sales) the right hand side result from our new expression.

    sum( Sales )    -----------> bottom(sum( Sales ) )

For each row the returned value is the same as the bottom value for this group: 30,60.90

  sum( Sales )    ----------->  top(sum( Sales ) )

For each row the returned value is the same as the bottom value for this group: 10,40,70.

Example 2
By specifying the second criteria as 2 , the returning value is the second value from the top or bottom of each group.

In this example top and bottom are giving the same result (the middle value) as we have 3 data points for each group

                sum( Sales )-----------> top sum( Sales ), 2 )                    sum( Sales )-----------> bottom( sum( Sales ), 2 )Example 3

        top(TOTAL sum( Sales ) )      bottom(TOTAL sum( Sales ) )Adding TOTAL before Sum will result with the first or last value to be assigned to all rows.(Total will ignore dimension)

Example 4

RangeAvg() takes 3 parameters-

expression ---> Top/Bottom(sum(Sales),

-offset of rows--->1-

number of rows to sum--->2 

                sum(Sales)                          rangeavg (Top(sum(Sales),1,2))

For each group in right table:

• Each row is average of values from two top rows for each group (first we are moving 1 row above current row and then adding 2 rows)

    sum(Sales)                          rangeavg (Bottom(sum(Sales),1,2))

                For each group in right table:

• Each row is average of values from two bottom rows for each group (first we are going 1 row down current  rowand then adding 2 rows)

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Missing Manual - Below() and Above()

How to use- Dual()

Missing Manual - Before() and After()

How to use - MissingCount()

Missing Manual - Top() and Bottom()

Working with Qlikview or QlikSense is like playing a good RPG or FPP game - you are working hard looking for  a solution and then you

picking a "Magic Weapon" and you are on next level.

"Did you know...?" is all about quick tips and tricks

So let me start.

Did you know that...

Can the cyclic group be used in an expression?

Did you?

That's good but if you did not - you may be surprised.

Recently I had to find a way to pick up values from Cyclic Group only to discover that after

if(GerCurrentField(...

Qlikview throws beautiful Red Lines.

Head down...fists tight. but wait.

What is the Community for?

My quest leads me to this topic

where our Superhero johnw posted the solution to my problem.

Because QV does not recognize its own syntax you are left out without IntelliSense.

Try to enter long expression - and you will be sweating and swearing...

The good point is - Its Work.

For those who are using personal edition:(with all credits to John) - please try yourself

Script:

dimension:

LOAD * INLINE [

    CategoryID, CategoryName

    1, Car

    2, CD

    3, DVD

    4, Laptop

    5, Software

    6, Sunglass

];

fact_sales:

LOAD * INLINE [

    CategoryID, SaleValue

    1, 1000

    2, 40

    3, 50

    4, 200

    5, 25

    6, 15

];

fact_cost:

LOAD * INLINE [

    CategoryID, CostValue

    1, 700

    2, 30

    3, 25

    4, 120

    5, 5

    6, 8

];

[Expression Group]:

LOAD * INLINE [

Sales, Cost

Sales, Cost

];

GUI:

1)

          Create new bar chart

2)

    Add dimensions:

  a) CategoryName

and

        b)Create new Cyclic dimension SalesCost with Cost and Sales as Dimensions.

3) Add Expression:

=if(getcurrentfield(SalesCost)='Sales',sum(SaleValue),sum(CostValue))

And Voila! you got your Magic Sword.

Good luck in your quests.

Feeling Qlikngry?

How To /Missing Manual(25 articles)

    Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

FieldIndex()

  This function belongs to two groups Chart Inter Record Functions

  and Inter-Record functions.

Returns the position of the field value value in the field fieldname (by load order). If value cannot be found among the field values of the field fieldname, 0 is returned. Fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes.

Data Model
(Copy and paste below code into the edit script window and reload)

LOAD * inline [

]

Now create a straight table and  remember to sort dimension and expression by load order

Returns position from value specified by second criteria (value)

• Returns positions of value being searched.
• field_name must be given as a string value (enclosed with single quotes ('))
• If value does not exist within column, 0 is returned

Syntax:

field_name - the name of your field/column

value - the value for which you want to know the position

Example

fieldIndex( 'Name', 'John Doe' )

Bonus

They said there is no lookup in Qlik front end?

Think again ...

=FieldValue('Helptext',FieldIndex('Name', 'John Doe'))

by wrapping FieldIndex with FieldValue we can return the unknown value of the 'HelpText' column that corresponds with the given value 'John Doe' of the Name column.

Feeling Qlikngry?

How To /Missing Manual(20+ articles)

                                          (  based on a true story)

Cast:

SUM(),ABOVE(),RANGESUM()

Case:

Return Average of last 3 months(not including current one)

Preparation:

Using data from attached Excel file and script from txt load data into Qlikview.

(Click on picture to see full size)

Create Straight Table (Layout-->New Sheet Object-->Chart...) -->

Use:

Year and Month

as dimension  -->

and Sales as expression.

(In this example Sales and SUM(Sales) deliver the same results as we have unique values in Month dimension. -->

Investigation:

The expression we are going to take apart is:

Rangeavg(Above(Sum(Sales),1,3))

Step 1) SUM()

The syntax of SUM in Qlikview  is quite complicated when used in charts:

(We not going to investigate that now so please refer to Qlikview Help for  more details)

For our example we only need:

SUM(Sales)

The result of this expression is:

Step 2) ABOVE()

ABOVE takes 3 parameters:

expression - which in our example is: SUM(Sales).(we are ignoring the total here)

offset- this is integer value(0,1,2...) to specify how many rows the whole expression should be move down in relation to current row

n - this is integer value(1,2...) and will return range of values.

Remember:

The last parameter will only work when used as argument with another Chart Range Function

(please see Qlikview Help for details)

The result of wrapping SUM with ABOVE

ABOVE(SUM(Sales),offset)

is  presented below.

Offset of 1,2 and 5 rows:

Step 3) Rangeavg()

This function returns average  of values or expressions:

Rangeavg(1,1) =1

Rangeavg(1,2,3)=2

RangeAvg(sum(1+2),sum(2+3)) is equal to RangeAvg(3,5)=4

Step 4)

So what actually is going on behind the scene we we use:

Rangeavg(Above(Sum(Sales),1,3))?

We can rephrase that expression to:

" Sum values from dimension Sales then go 1 row above and  take  values from 3 rows going up"

So based on our example :

Row one:

Will always return NULL

as we can not got above row 1 -->

Rangeavg()=NULL

Row two:

"Go up 1 row and return 3 values".

We can only go 1 row up and return 1 value

Rangeavg(2000) = 2000

Row three:

"Go up 1 and return 3 values going up"

We can only go 2 rows up:

Rangeavg (2000,3000) =

(2000+3000)/2=2500

Row four:

"Go up 1 row..."

This is first row where values from 3 rows above

can be returned(Row 1, Row 2 ,Row 3)

Rangeavg(Row 1, Row 2 ,Row 3) = Rangeavg(2000+3000+1000)=6000/3=2000

Row five:

Rangeavg(Row 2, Row 3 ,Row 4)

=Rangeavg(3000+1000+2500)=6500/3=2167

and so on...

The final result:

Conclusions:

The offset parameter of the ABOVE function according to Qlikview Help: