Aggr () in pivot table

[Anonymous]

Hi,

I have a pivot table. The points expression is not giving the sum of rows. I understand that I need to use the aggr() to get the correct sum of rows but I am not able to correctly place that function. Can anyone please help? Thanks!

Dimension is a calculated dimension :  If((isnull(WinLossDate) or num#(WinLossFiscalYear&num(WinLossFiscalQuarter,00))<'201201'),null(),Owner)

Points expression : 

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 100000, 0.000,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 100000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 250000 , 0.125,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 250000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 500000 , 0.310 ,

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 500000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 1000000, 0.625,

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 1000000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 2500000 , 1.25 ,3.100 )))))

[GabrielAraya]

Hi ...

Did you test ..

Sum ( Aggr ( if ( (sum({<FunnelStamp2={'Current'},Stage={' ......... ), [Project Owner], Dimension2, dimension3, ...
))

Regards

[Anonymous]

Try this:

sum(aggr(if(sum(Expression1)<60000,1,10),Dim2))

where Dim2 = hidden dimension ( + )

Hope it helps.

Fernando V.

[ToniKautto]

The expression looks over complicated. Wouldn't the belwo expression give the same result?

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 100000, 0.000,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 250000 , 0.125,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 500000 , 0.310 ,

if ((sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 1000000, 0.625,

if ((sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 2500000 , 1.25 ,

3.100 )))))

I would recommend that you try and add your calculated dimension to the data model during application reload, so that you get a real dimension in your chart. Then you can create a column total by using Aggr(), where you list all dimensions in your chart.

Sum(Aggr(<the expression above>, Dim1, Dim2, ... DimN))