Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This forum is where any loggedin member can create a knowledge article.
A question that gets asked regularly is how to calculate a rolling measure over a period of Nmonths (or weeks or days). For example a 12month rolling total or a 4week rolling average. There are several ways to do this. But these approaches have some limitations that need to be kept in mind. I'll try to explain these approaches and some of their limitations.
First let's load some sample data. The SalesData table below will contain sales amount values for twelve consecutive months.
SalesData:
load * inline [
Month, Amount
1,6
2,4
3,7
4,3
5,4
6,9
7,5
8,7
9,8
10,6
11,9
12,7
];
This is a very simple table with little data, but this enough for demonstration purposes.
Once this data is loaded it's possible to create a straight table chart object to display the amount per month and a running total. As expected Month is used as dimension. The expression sum(Amount) will display the amount per month. Now let's add an expression to calculate a running total over three month periods.
This can be done in two ways. The first uses the Accumulation option for expressions. The same expression sum(Amount) is used, but now the Accumulation option is set to 3 Steps Back:
The second option uses the rangesum function. That expression looks like this:
rangesum(above(sum(Amount),0,3))
This sums the Amount value on current row and on the previous two rows. The resulting straight table looks like this:
This looks good. The rolling 3 months amount is calculated correctly. But what happens if a selection of months is made?
The rolling 3 month amount for month 4 is now 3 instead of 14. This is because month 1,2 and 3 are no longer included in the calculation for the rolling 3 month total.
The accumulation option has another issue. It only works when only one dimension is used in the straight table. The rangesum expression can be modified so it can calculate across dimension borders, but the accumulation option can't. The modified rangesum expression adds the total keyword to the above() function:
rangesum(above(total sum(Amount),0,3))
This goes some way to doing what we want, but the issue of displaying the wrong rolling 3 month amount for month 4 isn't solved yet. Contrary to what I first thought there is a solution for this, as Henric pointed out to me in the comments below. By combining the rangesum with the aggr function it's possible to calculate the correct rolling 3 month amounts for each month. The expression needed for that looks like this:
sum(aggr(rangesum(above(total sum({<Month=>}Amount),0,3)),Month))
Read Elif's blog post Accumulative Sums for a more complete explanation.
How about set analysis expressions?
This expression should calculate the sum of amount for the three month period:
sum({<Month={'>=$(=only(Month)2)<=$(=only(Month))'}>}Amount)
But notice the only() function. This requires that only one month value is selected. After selecting month 4 the result looks like this:
This shows the selected month, but also the two previous months. And the values are not accumulated.
Ok, but what about the max function instead of only?
sum({<Month={'>=$(=max(Month)2)<=$(=max(Month))'}>}Amount)
That gives a different result, but still not what we're looking for:
Now only the last three months are shown and again the values are not accumulated.
The 'problem' is that the set is calculated once for the entire chart, not per row. This means that it's not possible here to use Month both as a dimension and in the set modifier in the expression.
There's still an option left to discuss: AsOf tables.
The AsOf table links a period with all the periods in the rolling period. In this example months are used, but it can be applied to any type of period like hours, days or weeks.
For the three month periods needed for a rolling 3 month total this means a month should be linked to itself, the previous month and the month before the previous month. The only exceptions are the first month, which is itself the rolling 3 month period, and the second month that together with the first month is its rolling 3 month period. There are no months before the first month so the first two months cannot run over 3 months.
The AsOf table needed for the rolling 3 month calculations looks like this:
This table can be created like this:
AsOfMonth:
load
Month as Month_AsOf,
Month + 1  IterNo() as Month
Resident SalesData
while IterNo() <= 3;
right join load Month Resident SalesData;
What this does is create three records for every month using the while statement. But that also creates three records for month 1 and 2. This would create a month 0 and a month 1. The right join is used to remove those incorrect month values.
Now that the AsOfMonth table is created the Month_AsOf field can be used instead of the Month field in the straight table. The expression for the straigh table is simply sum(Amount).
The straight table now shows the correct rolling 3 month total for month 4.
This can be expanded a little so not only the rolling 3 month can be shown, but also the amount for the month itself. To achieve this the AsOf table is modified by adding a field to label the type of period. And records are added to the table so each Month_AsOf value is linked to the matching Month value:
AsOfMonth:
load 'Current' as Type,
Month as Month_AsOf,
Month as Month
Resident SalesData;
Concatenate (AsOfMonth)
load 'Rolling 3' as Type,
Month as Month_AsOf,
Month + 1  IterNo() as Month
Resident SalesData
while IterNo() <= 3;
right join load Month Resident SalesData;
There are now two types of periods available: Current and Rolling 3. Additional period types can be added for example for Rolling 6, Rolling 12 month and YeartoDate periods. You can find examples of these types in the attached AsOf Table Examples.qvw document.
The period type can be used in the chart expressions to calculate the amount for the wanted period:
Current amount: sum({<Type={'Current'}>}Amount)
Rolling 3 month amount: sum({<Type={'Rolling 3'}>}Amount)
Concluding, there are two solutions that do what we want:
1. The rangesumaggr combination
2. The AsOf table
The first has the advantage that no changes to the data model are needed. It's also possible to dynamically change the period to aggregate over by using a variable instead of a hardcoded number of periods. A disadvantage is that that it's a somewhat complicated expression that also comes with a performance cost.
The AsOf needs changes in the data model to create the AsOf table and fill it with the necessary records. The advantage is that it likely performs better on large data sets. It's also quite versatile since you can add several sets of records to meet different scenario's. The expressions you end up with in the charts are also less complicated. That said, it will likely take you some time to fully understand the AsOf table concept and realize all the places where you can put it to good use.
In the end you'll have to decide for yourself which solution is appropriate in your situation. With regards to the performance of one or the other solution, you will simply have to test to discover if the performance is acceptable. But of course such testing is already part of your development process, right?
I'd like to thank John Witherspoon for introducing me to the AsOf tables concept and Henric for pointing out the solution using the rangesum function in combination with the aggr function.
How To / Missing Manual
Did You like it?
The blog posting 'The Power Of Yellow' describes how Show Alternatives can be used to highlight data excluded by a selection on a field that would otherwise be included. This QlikView application illustrates this functionality.
This tequnique can be useful in making immediately apparent something that would otherwise be hard to spot, or require extra clicks to remove selections.
For a full description of how and why the feature can be used please see the original blog post at: http://www.quickintelligence.co.uk/thepowerofyellow/
Steve Dark
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 onedimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column........
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:
sum(Sales) rangeavg (Below(sum(Sales),1,2))
For each group in right table:
Still feeling hungry?
Do you Qualify? How to use QUALIFY statement
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
Missing Manual  Below() and Above()
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:
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?
When showing data in a month on month style the current month can often have a large drop off. This can be prevented by providing a run rate for the current month. This is where the known values for the current period are extrapolated forward for the rest of the period, providing an estimate of where the period will end. For example, if you have a value of 1000 on day 10 of a 30 day month, you could calculate a run rate of 3000 for the month.
This solution was created in response to a question on Qlik Community, which you can find here:
Re: Last time value in time line chart
I have documented how this document works and the reasons why you might use it in a blog post here:
https://www.quickintelligence.co.uk/qlikrunrate/
There is also a link to the QlikView version of this application on the blog post, and further discussions on why run rates are good to have.
I hope that you find it useful. You will find other applications that I have uploaded under my profile on QlikCommunity.
Steve
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/qliktargetbarchart/
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
These quesions and others are answered in this Technical Brief.
Thank you, mellerbeck and Matthew Fryer for idea and inspiration.
See also
NULL – The Invisible Nothing and
20121218: Fixed an error in the section about NULL propagation pertaining to relational operators. Added examples in the same section. /HIC
20121220: Added information about ideographic space. Changed layout of some truth tables (images). /HIC
20130446: Added section about How QlikView displays NULLs
20161013: Corrected a sentence about the result of <Product = {}>
Definition:
Returns the number of dimension columns that have nonaggregation 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 

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?
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
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
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
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?
and four arguments:
<>
Preparation:
We have got two tables in Excel file.
For convenience let’s call them ‘Table1’ and ‘Table2’:
Table1  Table2 

Remember
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:
The final tables you should see are like this:
Pros:
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:
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?
Bullet points:
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 (Az) ‘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()) )

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
Data  Expression  Solution  Result 

=MinString( Mid(Country,2,2)) =MaxString( Mid(Country,2,2)) 
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
Data  Expression  Result 

=MinString({<Sales={2}>} Country)

😎 With Aggr()
For each Country return lowest and highest Category string value.
Data  Expression  Result 

=Aggr(Minstring(Category),Country) =Aggr(Maxstring(Category),Country) 
If know about other ways of using these functions please let me know.
This example shows a chart with a Dynamic Date Dimension, that shows a different date type based on the possible values of each date type.
Natively QlikView allows for Drill Groups of fields, which makes it simple to create a drill down from Years, through Months and down to Dates. This works well, but does not allow the user to view months across two years, or even some months at the end of one year and the beginning of the next.
By using a Calculated Dimension this example chart chooses which date type to show, Year, Month or Date based on the number of possible values for each of those date types. This allows for a much more dynamic and intuitive view of values over time.
Steve Dark
This QlikView shows how basic Set Analysis syntax can be used to deliver a view of Year To Date figures.
Two subtly different approaches are given, one using a Pivot table to give the months as a dimension, the other using a Straight Table. The first approach is the quickest and easiest to implement  whilst the other gives more flexibility (in this case to add both Total and Average columns).
The QlikView was originally written as a response to the following QlikCommunity thread: http://community.qlik.com/message/132639
Steve Dark
Dear all,
Sharing simple example of What if analysis in Qlik Sense using Slider extension
GitHub  mhamano/qliksenseslider: Qlik Sense extension for a slider object
Hope will be good start for beginners
Vikas
Hi Qlikers,
This document demonstrates the Bollinger Bands.
Many traders use Bollinger Bands to determine overbought and oversold levels, selling when price touches the upper Bollinger Band and buying when it hits the lower Bollinger Band.
Components of Bollinger Bands
1) Avg Stock Price
It's simple Avg of stock price.
2) Moving Avg of Stock Price
It's Moving Avg of Stock price over last n Period
3) Upper Band
Moving Avg of Stock Price + Std Deviation over last n Period * Std Deviation Multiplier
4) Lower Band
Moving Avg of Stock Price  Std Deviation over last n Period * Std Deviation Multiplier
Note :
1) User can select the Moving period and Std Deviation multiplier by own.
2) When you see the Bollinger Band by week, you can choose the Moving period from 1 to 50.
3) When you see the Bollinger Band by month, you can choose the Moving period from 1 to 10.
4) You can input the Std Deviation multiplier from 0.01 to 4.
Please see the attached Application
If you liked this then also see
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/infographicsinqlikviewvol2/
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
you can find a practical way of using them.
Feeling Qlikngry?
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/qlikviewdatastructures/
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/qlikviewexamples/
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
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 InterRecord functions and can only be used in the script (backend).
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 rowno argument.
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?