Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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?
Can you upload a qvf version?