Aggregate problem

[#1]

Hi,

I have the following formula in a pivot table:

if(Sum( total <Clienti.CliRiferimento> if(right(Competenza,4) = $(vAnnoRiferimento)-1, FatNetto_AP, 0))>0,

if(sum(total <Clienti.CliRiferimento, CoGe> if(right(Competenza,4) = $(vAnnoRiferimento)-1, FatNetto_AP))=0,

(sum(FatNetto))

)

)

the pivot result as this:

so the total are wrong in all column. If I change the table to stright and set Sum of Rows as total is working fine.

I tried some sum(aggr(.... but without getting the total running.

Can someone help?

Thanks!

[johnw · Accepted solution]

I can't see what dimensions your table uses, so all I can tell you is the general approach:

sum(aggr(YourCurrentExpressionHere, YourDimension1, YourDimension2, etc.))

[#3]

Hi John,

sorry I was in hurry so i didn't put enought information to explain what i need.

I attach a QV with the data example. The scenario is:

I need to divide the customers in 3 categories: New, New on Existing, Existing. The meanings are:

- New = For customers that have sales = 0 in year 2009 I should show the sales of 2010 (month by month)

- New on Existing = For customers that have sales > 0 on year 2009 I should show the amount of sales only for the products where the total sales in 2009 = 0

- Existing = sales 2010 - New on Existing - New

Hope that now is clear.

Thanks