Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arvinserco
Contributor II
Contributor II

Sum Total with multiple conditions

Hi all,

Row Labels Negative Positive
AA -2400792.828 567974.053
BB -318395.583 24996.186
CC -7764599509 176429.394
Grand Total -7767318697 769399.633

 

I need to calculate the percentage of each "row labels" out of Grand Total  for both Negative & Positive Category. Also I need to use the filter as "Class1" from one of the column's (by using which I get the above Data table). I need to put all these conditions in one expression.

If anyone can help let me know.

1 Solution

Accepted Solutions
sunny_talwar

Try this

For Positive: Sum({<Margin={'Positive'}, Region = {'Class1'}>}sales)/Sum({<Region = {'Class1'}>} TOTAL <Margin>Sales)
For Negative: Sum({<Margin={'Negative'}, Region = {'Class1'}>}sales)/Sum({<Region = {'Class1'}>} TOTAL <Margin>Sales)

View solution in original post

11 Replies
sunny_talwar

What is your expression right now for Negative and Positive? Also, what is the expected output? Can you share that as well

arvinserco
Contributor II
Contributor II
Author

Output should be this:
Row Labels Negative Positive
AA 0.031% 73.820%
BB 0.004% 3.249%
CC 99.965% 22.931%

Expression used as of now :
Sum(sales)/Sum(TOTAL Sales)
Note: Negative & Positive, Row labels are in two different columns in data and I need it in above format.. By using above calculation as measure and other fields as dimensions.
sunny_talwar


@arvinserco wrote:
Note: Negative & Positive, Row labels are in two different columns in data

What do you mean? do you have two fields called? Negative and Positive or are they part of a single field?

arvinserco
Contributor II
Contributor II
Author

they are part of single field.....
arvinserco
Contributor II
Contributor II
Author

It is something like this:

Row LabelsMarginSales
AANegative-2400792.828
AAPositive567974.053
BBNegative-318395.583
BBPositive24996.186
CCNegative-7764599509
CCPositive176429.394
Grand Total -7766549298

 

sunny_talwar

Try this

Sum(sales)/Sum(TOTAL <Margin> Sales)

arvinserco
Contributor II
Contributor II
Author

Thanks Sunny,

I got the expression now:
For Positive: Sum({<Margin={'Positive'}>}sales)/Sum(TOTAL <Margin>Sales)
For Negative: Sum({<Margin={'Negative'}>}sales)/Sum(TOTAL <Margin>Sales)
And if you can help me with one more condition if I have to use a filter from one of the fields namely.... if Filter = "Class1" in the expression.

Then this whole things covers up.

Thanks 🙂
sunny_talwar

I am not sure I follow?

arvinserco
Contributor II
Contributor II
Author

@ sunny not a problem.... See below data.

Region Row Labels Margin Sales
Class1 AA Negative -2400792.828
Class1 AA Positive 567974.053
Class1 BB Negative -318395.583
Class1 BB Positive 24996.186
Class1 CC Negative -7764599509
Class1 CC Positive 176429.394
Grand Total -7766549298

There is one more column "Region" in which there are different regions but I need to filter only "Class1" region. How should I use this filter in above expression.
For Positive: Sum({<Margin={'Positive'}>}sales)/Sum(TOTAL <Margin>Sales)
For Negative: Sum({<Margin={'Negative'}>}sales)/Sum(TOTAL <Margin>Sales)

NOTE: I got the answer but I need to use a filter extension and choose "Class1". All i want to use this filter in calculation itself. I hope you got my point