ValueList() – For those tricky situations

[Alexander_Thor]

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?

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.

[#2]

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?

[datanibbler]

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.

[nicolett_yuri]

thank you, it helped me a lot!

[#5]

#Awesomeness. Second the elegant comment.

[#6]

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 ?

[#7]

good article

[sohailansari201]

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,

[kkkumar82]

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

[#10]

Hi Alexander,

Do you have an example of this table in qvw?

Thank you.

[#11]

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