Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

How can I add totals for each month on pivot table

The totals for this pivot table are proving tricky to manipulate. Another issue is that the total price EUR-probability is grouped for each invoice of the same price. If I can get the totals for each month right then this won't be a problem. Any ideas

1 Solution

Accepted Solutions
Highlighted

Re: How can I add totals for each month on pivot table

Try putting Sum(Aggr()) around your expression

Sum(Aggr(

((win_probability)/100)*(if(currency='GBP',EachMonthAmount*$(GBPtoEUR),EachMonthAmount))

,YourDimensionsSeparatedByComma))

8 Replies

Re: How can I add totals for each month on pivot table

What is your expression?

Partner
Partner

Re: How can I add totals for each month on pivot table

Hi,

Enable the Partial sum for MonthYear and check

Partner
Partner

Re: How can I add totals for each month on pivot table

((win_probability)/100)*(if(currency='GBP',EachMonthAmount*$(GBPtoEUR),EachMonthAmount))

The expression calculates the adjusted value based on the probability of a win, if the currency is in GBP that is converted to EUR. EachMonthAmount is created in the script it takes the number of months between the start date and the invoice date and divides the sum into equal parts spread between the months between the two dates.

Partner
Partner

Re: How can I add totals for each month on pivot table

I have tried that, but to no avail...

Partner
Partner

Re: How can I add totals for each month on pivot table

Pivot Tableinc.PNG

devarasu07
Honored Contributor II

Re: How can I add totals for each month on pivot table

Hi,

Can you share your app with mock data set?. it's easy for us to check and help you quickly. Thanks

Regards,

Deva

Highlighted

Re: How can I add totals for each month on pivot table

Try putting Sum(Aggr()) around your expression

Sum(Aggr(

((win_probability)/100)*(if(currency='GBP',EachMonthAmount*$(GBPtoEUR),EachMonthAmount))

,YourDimensionsSeparatedByComma))

Partner
Partner

Re: How can I add totals for each month on pivot table

Please use Sunny Ts response, but ensure you use all the dimensions to your left, and put partial sum against your first dimension.