Qlik Community

QlikView Documents

Documents for QlikView related information.

Missing Manual - ValueLoop() & ValueList()

Missing Manual - ValueLoop() & ValueList()

1f4fe19.jpg

    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

2015-08-19_025244.png

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.

2015-08-19_004233.png

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 1Example 2Example 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

2015-08-25 07_56_30-QlikView Personal Edition - [QV1].png2015-08-25 07_58_37-QlikView Personal Edition - [QV1].png2015-08-25 07_59_56-QlikView Personal Edition - [QV1].png


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

2015-08-19_023417.png

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


list_ingredients.png

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

2015-08-18_170122.png

v1 - list of values

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

Header 1Header 2
valuelist ( 1, 10, 100 ) 2015-08-26 14_30_06-Edit Document Missing Manual - ValueLoop() &am... _ Qlik Community.png
Header 1Header 2
valuelist ( 'a', 'xyz', 55 ) 2015-08-26 14_30_19-Edit Document Missing Manual - ValueLoop() &am... _ Qlik Community.png

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.

2015-08-25 00_38_57-QlikView Personal Edition - [C__Users_Katarzyna_Desktop_Files_vpl.qvw_].png

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

2015-08-25 00_47_25-Chart Properties [Sum(Values)].png

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

2015-08-25 23_10_09-Chart Properties [1.625].png

and then use nested IF statement to create your Metrics

2015-08-25 23_12_14-Edit Expression.png

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

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

to get below result:

2015-08-26 14_10_16-Edit Expression.png

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

2015-08-26 14_08_41-Edit Expression.png

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)

Comments
kuczynska
Contributor III

Thanks for sharing! Interesting article, finally clarifying some of my questions regarding ValueList()!

kristof_j
Contributor II

I used this in my dimension to get a list of all the dimensions

=ValueList( $(=Concat(chr(39)& $Field & chr(39) , ', ' )) )

Would it be possible to make a second dimension with the values of these dimensions?

Then it would be possible to count the number of records you have for each value. This opens possibilities to compare groups in different states.

Chanty4u
Esteemed Contributor III

Nice post

jonas_rezende
Valued Contributor

Very good.

Not applicable

Muy buen aporte.

Por fin he entendido ValueList() y ValueLoop()

0 Likes

De nada. Feliz de ayudar.

0 Likes
beck_bakytbek
Honored Contributor

Thanks for sharing

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-08-12 04:50 AM
Updated by: