Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem using AGGR() with a ValueList()

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?

3 Replies
nathanfurby
Specialist
Specialist

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.

vikraant
Creator
Creator

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?

SujanJoeJacob
Contributor II
Contributor II

Can you upload a qvf version?