Why the totals are not calculated in pivot table?

[#1]

I'm trying to do a table related contracts.

In my Pivot Table I put as dimensions:

- Country

- contract number

- Current Year Month (a variable that it is only the list of year-month like 2016-01; 2016-02;2016-03 ....)

in the Expression I put the following formula

=(If(CURRENTYEARMONTH<ContractFromYYMM,  0,

(if(CURRENTYEARMONTH=ContractFromYYMM,  ((DAYS-ContractFromDD)*DailyTotalAmount),

     (IF(CURRENTYEARMONTH>ContractFromYYMM and CURRENTYEARMONTH<ContractToYYMM,(DAYS*DailyTotalAmount),

     (if(CURRENTYEARMONTH=ContractToYYMM, ( ContractToDD*DailyTotalAmount),0)) ))))))

This works but when in the presentation filed I "tick" Show Partial Sums for each Countries the SUM is 0.

If I put the SUM operator at the beginning of the formula it works partially. (Only where in the IF I put the "=" as operator.

Could you please give me an idea how to proceed to fix it?

[sunny_talwar · Accepted solution]

Can you try this:

=Sum(Aggr((If(CURRENTYEARMONTH<ContractFromYYMM,  0,

(if(CURRENTYEARMONTH=ContractFromYYMM,  ((DAYS-ContractFromDD)*DailyTotalAmount),

    (IF(CURRENTYEARMONTH>ContractFromYYMM and CURRENTYEARMONTH<ContractToYYMM,(DAYS*DailyTotalAmount),

    (if(CURRENTYEARMONTH=ContractToYYMM, ( ContractToDD*DailyTotalAmount),0)) )))))), Country, [contact number], [Current Year Month]))

[#3]

thank you very much for the help but it doesn't work.

using the formula

=SUM(Aggr(

(If(CURRENTYEARMONTH<ContractFromYYMM,  0,

(if(CURRENTYEARMONTH=ContractFromYYMM,  ((DAYS-ContractFromDD)*DailyTotalAmount),

     (IF(CURRENTYEARMONTH>ContractFromYYMM and CURRENTYEARMONTH<ContractToYYMM,(DAYS*DailyTotalAmount),

     (if(CURRENTYEARMONTH=ContractToYYMM, ( ContractToDD*DailyTotalAmount),0))

     )))))), Country, [DB_ContractID], [CURRENTYEARMONTH]))

the result is still 0

[sunny_talwar]

Do you have more than those three dimensions in your pivot table? From the looks it seems that you do. Can you try adding all of those dimension withing your Aggr() function?

Also, don't mark a question correct unless the issue is resolved because if you do mark it with the correct answers, other might just ignore the post assuming that it has already been answered.

[#5]

I added all dimensions and now it works

Thank you for your help