topic Problem using AGGR() with a ValueList() in QlikView

Problem using AGGR() with a ValueList()

Thu, 27 Jan 2011 20:56:59 GMT

I need to use AGGR() in more than one dimension of a ValueList but can't seem to get it to work.

Consider the following code:

/* Test 1 */
IF(ValueList('%Count','Sales','Margin')='%Count'
 ,NUM(SUM(IF(AGGR(SUM(Sales-Cost)/SUM(Sales),MaterialNo)>0,1))
 /COUNT(DISTINCT MaterialNo),'#0.0%')
,IF(ValueList('%Count','Sales','Margin')='Sales'
 ,NUM(SUM(IF(Sales>0,Sales)),'$#,###,##0')
,IF(ValueList('%Count','Sales','Margin')='Margin'
 ,NUM(SUM(IF(Sales-Cost>0,Sales-Cost)),'$#,###,##0')
)))

I get correct values for all three dimensions.

Now consider this:

/* Test 2 */
IF(ValueList('%Count','Sales','Margin')='%Count'
 ,NUM(SUM(IF(AGGR(SUM(Sales-Cost)/SUM(Sales),MaterialNo)>0,1))
 /COUNT(DISTINCT MaterialNo),'#0.0%')
,IF(ValueList('%Count','Sales','Margin')='Sales'
 ,NUM(SUM(IF(AGGR(SUM(Sales-Cost)/SUM(Sales),MaterialNo)>0,1))
 /COUNT(DISTINCT MaterialNo),'#0.0%')
,IF(ValueList('%Count','Sales','Margin')='Margin'
 ,NUM(SUM(IF(Sales-Cost>0,Sales-Cost)),'$#,###,##0')
)))

[Note that the calculation for 'Sales' is now identical to the calculation for '%Count'.]

The values for '%Count' and '%Margin' are correct, but the row 'Sales' is not displayed.

Any ideas why I can't get the 'Sales' row to appear?
Is there some restriction on ValueLists where only one AGGR can be used?

Re: Problem using AGGR() with a ValueList()

nathanfurby — Mon, 19 Sep 2011 15:31:11 GMT

I had the same problem and it seems the AGGR function does not like it when a ValueList is used in dimension. Indeed it doesn't even like it when you use an island dimension. But I managed to create a workaround by using the island dimension in the AGGR function.

I am attaching a modified qvw of the example above for anyone in the future who is also looking for a solution to this problem. I added DummyDimension in the load script and created a copy of Test2 chart and called it Test4.

Re: Problem using AGGR() with a ValueList()

vikraant — Tue, 16 Oct 2018 07:53:28 GMT

I am also facing the same issue while I am using Aggr with ValueList . I really cannot substitute ValueList with inline table. Any other workaround found yet?

Re: Problem using AGGR() with a ValueList()

SujanJoeJacob — Tue, 16 Mar 2021 04:26:55 GMT

Can you upload a qvf version?