Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Alexander_Thor
Employee
Employee

Have you ever been asked to create a table that has several independent calculations over different metrics?

Mixing aggregation formulas and counts in the same table?

Did you end up creating different tables for every view point on the same information?

Or did you create a table like the one below?

table.PNG

If not, let me introduce you to ValueList() and its number oriented big brother ValueLoop().

ValueList
ValueList (value {, value })

ValueList allows us to specify a set of arbitrary values within the function, when used as a calculated dimension in a chart this will act as a synthetic dimension.

We can later restate the same function, with the same parameters, in our expression to reference the corresponding value in our newly created synthetic dimension.

And it is as simple as creating a straight table with following dimension and expression

Calculated Dimension:

=ValueList('My First KPI','My Second KPI')

Expression:

=IF( ValueList('My First KPI','My Second KPI')='My First KPI',

     Sum([My First KPI Field],

     Count([My Second KPI Field])

)

And voila we have created a table/chart with a dimension that does not exist in our data model and with an expression that has the possibility to mix and match aggregation functions over each dimension.

Matthew Crowther has also created an excellent Explosion Chart that also leverages ValueList, you can read more on his blog

ValueLoop
ValueLoop(from [, to [, step = 1 ]])

ValueLoop shares the same characteristics as it’s little brother ValueList with the exception that it will create a series of numbers as the synthetic dimension.

To create a dimension with values that spans between 1-100 we would create a calculated dimension with

=ValueLoop(1,100,1)

which we can reference from our expression with the expression

IF( ValueLoop(1,100,1)=3,

     'Almost Pi',

     'Not Pi'

)

ValueLoop also allows us to create the, not so useful but fun to make, square pie chart which you can read more on in this technical brief.

26 Comments
Not applicable

I really like this, but I have some problems using it together with set analysis.

In my example I have "Year", "Month" and "YEAR_MONTH". Selected is Year = 2013 and YEAR_MONTH = 2013-09. I have a Straight Table with "Month" and ValueLoop(1,2). Then I have this expression:

Pick(ValueLoop(1,2),

sum({$<YEAR_MONTH=>} NO_OF_CONTRACTS_DC)/1000/2,

sum({$<YEAR_MONTH=>} 1)

)

I'm expecting it to return something for all months of 2013. But I only get figures for 2013-09?!

Can someone explain to me why?

0 Likes
8,864 Views
datanibbler
Champion
Champion

Awesome!

That could really help in making my apps more elegant.

Right now, I have diagrams with n lines which have to apply a different calculation in every line. I use the PICK(MATCH()) function to keep it all somewhat orderly (with the bracketing), but still it is quite resource-heavy.

Best regards,

DataNibbler

P.S.: Oh - no, I have misunderstood it. Upon reading the document a second time, I realize that this actually has the effect of an Inline_table with some values (to be used as a dimension) that we don't otherwise have.

The charm of this is in the fact that it makes the thing much more flexible since (I guess) the contents of the ValueList() function could even be loaded from an Excel file, thus really letting the users decide which field (provided that is available) they want summed or counted or whatever.

That would add some real ad-hoc-reporting-capabilities to our QlikView apps.

0 Likes
8,864 Views
nicolett_yuri

thank you, it helped me a lot!

0 Likes
8,718 Views
Not applicable

#Awesomeness. Second the elegant comment.

0 Likes
8,718 Views
Not applicable

If my valuelist has two same values will it be a problem to calculate their measures.

For example my valuelist has two values Wholesale (this one is for last year) and Wholesale (this one is for current year). Will there be a problem while calculating the measure of these dimensions ?

0 Likes
8,718 Views
Not applicable

good article

0 Likes
8,718 Views
sohailansari201
Creator
Creator

Hi,

I am having a unique issue while using valuelist() in a straight table that I am unable to find any help with:

My Dimension:

Valuelist('A','B','C')

My Expression:

pick(match(valuelist('A','B','C')

  'A','B','C')

,sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) > 10000, 1,0), storeID))

,sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) < 10000, 1,0), storeID))

,sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) = 0, 1,0), storeID))

)

But the Result is coming out to be:

A     54

B     0

C     0

No matter in which order I keep my 3 formulas, only the first one works and rest of them comes out as zero. However, if I create three text boxes and put these 3 formulas in each of these text boxes, I can see the results of all three formulas. (I am scratching my head ) Please help.

Thank you,

8,718 Views
kkkumar82
Specialist III
Specialist III

Hi All,

Can we mix regular dimension with value list or value loop. for eg Supplier as first dimension and value list as second dimension.

Thanks

Kiran Kumar

0 Likes
8,718 Views
Not applicable

Hi Alexander,

Do you have an example of this table in qvw?

Thank you.

0 Likes
8,716 Views
Not applicable

Hi Sohail,

Have you found a solution to your problem. I found the same issue with a table I am building. The second row is always zeros. It all works for the first row and no matter if I change the order, it always works for the first row.

The formulas used inside the table work perfectly in isolated text boxes outside the table.

If you learned anything new about this, please let me know.

Thanks,

Alexis

0 Likes
8,716 Views