AGGR() issue

[#1]

Hi community,

I have got an issue with the AGGR function in a pivot table. I reproduced the issue in the attached example, which is a simplified version of the real issue.

The idea is to select a year first using the check boxes in the top left corner. The pivot table contains two dimensions and four expression. Two expression for the year selected and two expression for the year before the year selected. The first expression of a year (YYYY SUM) gives us a simple sum of the amount for both dimensions (Niv1 and Niv2). The second expression of a year (YYYY AGGR) gives us a sum of the amount for the first dimension (Niv1) only using the AGGR() function.

The AGGR() function however is not working for Niv1 = N3 and 2011. Probably this is caused by the selection of 2012 and the fact that there is no data for Niv1 = N3 and 2012. Is there a way to overcome this issue without modifying the year selection (this is mandatory in the application)?

[Gysbert_Wassenaar]

Try this instead of the aggr:

2012 AGGR: Sum(total <Niv1> Amount)

2011 AGGR: Sum({<Jaar={$(#=Jaar-1)}>} total <Niv1> Amount)

[#3]

Thanks for your reply Gysbert, it is helpful, but it is not the solution to the issue.

The example attached to this discussion lacks a calculated dimension. The calculated dimension contains values like ‘N1’,’N2’, etc., but also some NULL values. These NULL values are suppressed, but the corresponding Amount should be taken into account for the “YYYY AGGR” expressions. This is not the case when using your “total ” suggestion.