Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Partner
Partner

Trouble with the TOTAL function

I am building a pivot table where the measure (NetAmount for example) is of the type:

= (Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount)

I also need another measure (lets call it NetAmount(%) ) which represents the part of each row to the total.

If the measure was as simple as Sum(Amount) then the % would be calculated as:

Sum(Amount) / Sum(Total Amount)

However in this case  the Sum part is not so simple and  I am having trouble with the TOTAL part:

The following versions didn't give me the correct results:

a)

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
((Sum(Total{<[TransactionType] = {'I'}>} Amount)
- (Sum(Total{<[TransactionType] = {'C'}>} Amount))

b)

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
Sum
(Total(
((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))))

Any suggestions as to how I can achieve the desired result?

Thanks in advance

 

1 Reply
Highlighted
bharathadde
Contributor II

Re: Trouble with the TOTAL function

Try this and let me know if it works

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
((Sum({<[TransactionType] = {'I'}>} Total <TransactionType> Amount)
- (Sum({<[TransactionType] = {'C'}>} Total <TransactionType> Amount))

or may be  (I'm thinking you need group by any particular field)

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
((Sum(Total <Groupbytype(ex:Month)> Amount)
- (Sum(Total <Groupbytype(ex:Month)> Amount))