Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Why the totals are not calculated in pivot table?

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?

1 Solution

Accepted Solutions

Re: Why the totals are not calculated in pivot table?

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]))

4 Replies

Re: Why the totals are not calculated in pivot table?

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]))

Not applicable

Re: Why the totals are not calculated in pivot table?

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

Re: Why the totals are not calculated in pivot table?

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.

Not applicable

Re: Why the totals are not calculated in pivot table?

I added all dimensions and now it works

Thank you for your help