Aggregation problem when using GetCurrentField

[#1]

Hello experts,

I am trying to do an aggregated price variance analysis on cyclic dimensions. E.g. it should be possible to see this analysis on main group, product group, product line etc. In the example below there is one row where we do not have a dimension name for the main group of the granular data.

Now I am struggling with getting the correct aggregation in the chart.

In Sales Price Variance 2 - the detailed lines show the correct value but incorrect aggregated sum.

In Sales Price Variance - the aggregation works fine but I am missing values from the Main group where dimension name is NULL.

Attached is the spreadsheet with the formulas used.

Hope anyone can guide me on the correct path here.

Best regards,

Freddy

[gsbeaton]

Hi Freddy,

I've had similar issues and found solutions, so I hope I can help.

Firstly, I don't see how you are using getCurrentField().  You also mention you are using a cyclic group so I suspect you may be using getCurrentField() to check which dimension the user is currently viewing.  If this is the case, then you will need to ensure that your AGGR statement is picking up the correct dimension and then grouping on this as the user changes the dimension.  EG:

aggr(count(transactions)), $(vCurrDim), custid)

Note the use of the vCurrDim variable within the expression to inject the correct field depending on the selected dimension.

The other issue I have had is getting the desired figure in the Total row or column.  Sometimes I have found that I have had to create this manually.  The trick there is to use a different expression for the Total compared to the rest of the column.  You can do this by using the RowNo() function within an IF() statement.  The Total row is always reported as Row number zero:

IF(RowNo()=0
,//--Some expression to produce a single Total
,//--A second function to use for the rest of the column.
)

I hope that helps you get to a solution.

Kind regards

George