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: 
Not applicable

Sumif and Rangesum Question

Class

YearAmount
Turnover20141000
Turnover2013800
Admin expenses2014310
Admin expenses2013276
COGS2014250
COGS2013326
Finance costs20143.5
Finance costs201311
Operating expenses201412
Operating expenses201340

The above data is from my table. Would anyone teach me some simple techniques?

1. Sumif condition (Year = 2014, Class=Turnvover and COGS)

    I have tried: sum(if(Class='Turnover' and Class='COGS',Amount)) but it is not work

2. Answer for 2013 Gross Profit

     I have tried: Rangesum(Class='Turnover', Amount) - Rangesum(Class='COGS', Amount)

Can anyone correct my mistake, many thanks!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

1)

for max year

sum({$<Year={$(=max(Year))},Class={Turnover,COGS}>} Amount)

for 2014

sum({$<Year={2014},Class={Turnover,COGS}>} Amount)

2)

for max year

sum({$<Year={$(=max(Year))},Class={Turnover}>} Amount)

-

sum({$<Year={$(=max(Year))},Class={COGS}>} Amount)

for 2013 replace with 2013

View solution in original post

9 Replies
ecolomer
Master II
Master II

For the first question expression is

=Sum(if(Year=2014 AND (Class='COGS' OR Class='Turnover'), Amount))

And for the second:

= Sum(if(Class='Turnover', Amount)) - Sum(if(Class='COGS', Amount))

You can use also Set Analysis

Not applicable
Author

Thanks for your answere, however it doesn't work.

it says error in expression

ecolomer
Master II
Master II

=Sum(if(Year=2014 AND (Class='COGS' OR Class='Turnover'), Amount, 0))

= Sum(if(Class='Turnover', Amount,0)) - Sum(if(Class='COGS', Amount, 0))

maxgro
MVP
MVP

1)

for max year

sum({$<Year={$(=max(Year))},Class={Turnover,COGS}>} Amount)

for 2014

sum({$<Year={2014},Class={Turnover,COGS}>} Amount)

2)

for max year

sum({$<Year={$(=max(Year))},Class={Turnover}>} Amount)

-

sum({$<Year={$(=max(Year))},Class={COGS}>} Amount)

for 2013 replace with 2013

ecolomer
Master II
Master II

See solution from Massimo, is more efficient with Set Analysis

ashwanin
Specialist
Specialist

Set analysis answer by Massimo is better to use and easy to understand. You can update or modify it in future , in case your requirement changes. Attached is qvw for this.

Not applicable
Author

Thank you very much for your help. I can solve it.

Not applicable
Author

Thanks again.

maxgro
MVP
MVP

When you think you get the answer, please close this discussion by giving Correct and Helpful answers to the posts which are useful for you.  It helps others in finding answers for similar scenarios. Thanks and good Qlik.