Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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

View solution in original post

4 Replies
sunny_talwar

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

View solution in original post

Not applicable
Author

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.

Not applicable
Author

I added all dimensions and now it works

Thank you for your help