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 techniques are there to generate “new” tables; tables that don’t exist in the source data?
• How do I loop over one and the same record in source data?
• How do I propagate a value from above record to the current one?
• How do I populate a sparsely populated field?
• How do I generate all combinations – the Cartesian product – of two or more fields?
• How do I make simulations in QlikView?

These questions and others are answered in this Technical Brief.

HIC

Hierarchies are an important part of all business intelligence solutions, used to describe dimensions that naturally contain different levels of granularity. Some are simple and intuitive whereas others are complex and demand a lot of thinking to be modeled correctly.

• What is the difference between a balanced and an unbalanced hierarchy?
• How can a hierarchy be loaded into QlikView?
• Which data model should be used?
• How do I create an authorization scheme using a hierarchy?
• How can I check for data integrity problems in the hierarchy data?

These questions and others are answered in this Technical Brief. Attached you will also find a sample unbalanced, n-level hierarchy along with the script to load it.

See also the following blog posts:

Unbalanced, n-level hierarchies

Authorization using a Hierarchy

HIC

2013-11-26: Added section on authorization.

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()

This technical brief provides an overview of the new Skia graphics rendering library, which was implemented in QlikView in the November 2018 release. 

    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.

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()

    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)

How to - Dynamic Look and Feel

The concept here is to have colors and logo dynamically changed based on a listbox.

Steps:

1)

Download attached package and saved included picture to a folder.

2)

Create new application and copy and paste below code:

Company:

load * inline [

PickName,Company,val

pepsilogo.jpg,Pepsi,1

cokelogo.jpg,Coke,2

catlogo.jpg,Cat,3

]

;

Colors:

load * inline [

No.,      Pepsi,    Coke,Cat,Variable Name,Info

1, 'RGB(232,17,45)','RGB(184,19,33)','RGB(0,0,0)',vTabActiveBg,Active Backround

2, 'RGB(0, 133, 202)','RGB(51,11,12)','RGB(255,223,0)',vTabInactiveBg,Incative Background

]

;

T1:

load  * inline [

Product,Value,Country

A,1,Russia

B,2,UK

C,3,Poland

D,4,Germany

E,5,Czech

]

;

pics:

bundle load * Inline

[

PickName,Path

pepsilogo.jpg,'D:\Multilanguages\pepsilogo.jpg'

cokelogo.jpg,'D:\Multilanguages\cokelogo.jpg'

catlogo.jpg,'D:\Multilanguages\catlogo.jpg'

]

3)

Change this part

'D:\Multilanguages\catlogo.jpg'

to  a path with a folder with your pictures.

Reload.

4)

Create:

a) List box -  with field "Company"

b) Bar chart -- with "Product" as Dimension

                        and SUM(Values) as Expression

c) Text box-

with Representation as "Image"

and below expression in Text box:

=if(Company=Company,Info(PickName),'NA')

5)

Add 3 variables:

a) vTabActiveBg

      =$(=fieldvalue($(vCompany),1))

b) vTabInativeBg

  =$(=fieldvalue($(vCompany),2))

c) vCompany

      =Chr(39)&Company&Chr(39)

6)

In the Caption section of an object change the Base Color of " Active " and "Inactive Colors" to

"Calculated" with an expression

for "Inactive Caption" use:

=vTabInactiveBg

For "Active Caption"

=vTabActiveBg

The final picture will be similar to this one

Now you can use  the list box to change the colors and the logo of the company.

As you probably realize at this point, you can add  more variables to have every other color dynamically change this way.

Feeling Qlikngry?

How To /Missing Manual(23 articles)

The native  QV gauges are not very sexy but you can give them little makeover.

Please see below one on how to do that

(you can find out more her

Dislike gauge charts? You may want to give them a second chance)

The idea here is to have our scale started at 12 am (or pm).

We will need two gauges precisely layout out one on another.

The first gauge is just a circle,  the second will hold calculation and segments.

Advice:

Turn on the " Design grid" option.

It is very helpful when deciding upon the size of the chart or adjusting the layout.

First Gauge

Press right button anywhere on the tab and from option choose "Create New Sheet Object.." and then "Chart..."

a) Tab : General :-

Choose gauge from the chart options.

b) Dimension:

Skip

c) Expressions:

Add '1' as expression

d) Sort:

Skip

e) Style:

Choose the first top left corner chart

f) Presentation:

Fallow the picture below:

g) Actions:

Skip

h) Colors:

The  "Color Background" and the "Frame Background " "Transparency" must be set to 100%

i) Layout:

The "Use Borders " box must be empty

j) Caption:

The "Show Caption" box must be empty

The final result is a  blue circle.

Second gauge

Points a-e

(Follow instruction on first chart)

f) Presentation:

Follow the picture below.

Where Segment 1 Lower Bound is 0  and color is Blue and Segment 2 Lower Bound is is your expression

(in this example only decimal number is use but normally you will need to enter an expression in form x/y)

and the color is transparent.

Add Text in "Text in Chart"  option and format it as %.

Segment 1	Segment 2

Points g-j

Follow instruction for the first chart.

The final result:

And the two gauges joined together:

and a small visualization

----------------------------

Did You like it?

Feeling Qlikngry?

How To /Missing Manual(18 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:

Step by Step Guide how to Show/Hide Zero Values using buttons.

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 & 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()

Playing recently with many functions I have found out a way to get a value from a table  based on two fields

This solution will work like Excel INDEX/MATCH/MATCH with hard coded vertical dimension and dynamic horizontal dimension.

Very useful when you have  large data set but want to return only one value per category

Data Model

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

t1:

Load * inline [

Helptext, Name, City, Salary ,Sales

Block, Bob, Warsaw, 100, 25

Color, John Doe, Budapest, 200, 358

Topics, Jenny, London, 300, 400

Font, Curt, Cair, 400, 800

Data ,Martin, Madrit, 500, 1500

Group ,Mirek, Mins, 600, 200

Total ,Stan ,Utah, 700, 158

]

;

t2:

load * inline [

Category

Helptext

Name

City,

Salary,

Sales

]

First table is our Data Table the second works as Headers of the dimensions.

Create two ListBoxes:

'Category' and 'Name'

create Textobject:

and add this expression into Text window:

=if( (GetSelectedCount(Category)+ GetSelectedCount(Name))<2
,'Please choose  value from two fields',fieldvalue(GetFieldSelections(Category)
,FieldIndex( 'Name', GetFieldSelections(Name))))

Press 'OK'

Your result should be similar to this:

What's does the formula do:

GetFieldSelections(Name) - is returning value (Name) from the Name field    'Jenny'

Let say we have choose Jenny

FieldIndex( 'Name', GetFieldSelections(Name))                     will return position of Jenny in the Name field

                         =

FieldIndex( 'Name', GetFieldSelections(Jenny)) = 3

GetFieldSelections(Category) this will return value from Category Field ='City'

fieldvalue(GetFieldSelections(Category) ,FieldIndex( 'Name', GetFieldSelections(Name)))

                                                                         =

fieldvalue(GetFieldSelections('City') ,FieldIndex( 'Name', GetFieldSelections('Jenny')))

                                                                     =

                                                                  London

which we can check in straight table:

The rest of the code is a wrapper which will ensure that only when value from two fields are choosen the result will be displayed.

Thanks:

sergey.makA8

for simplification:

=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',$(=Category))

HaimantaDawar

for pointing that this expression works only with unique values

For multiple values use:

sergey.makA8

=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',Concat(DISTINCT $(=Category),', '))

QlikView has an intelligent algorithm to recognize dates independently of which region you are in. In most cases, you will have no problems loading them. It just works and you do not need to think about it. However, in some cases dates are not properly recognized and then you need to add some code in the script to make it work.

HIC