Sumif and Rangesum Question

[#1]

Class	Year	Amount
Turnover	2014	1000
Turnover	2013	800
Admin expenses	2014	310
Admin expenses	2013	276
COGS	2014	250
COGS	2013	326
Finance costs	2014	3.5
Finance costs	2013	11
Operating expenses	2014	12
Operating expenses	2013	40

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!

[ecolomer]

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

[#3]

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

it says error in expression

[ecolomer]

=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 · Accepted solution]

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]

See solution from Massimo, is more efficient with Set Analysis

[ashwanin]

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.

[#8]

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

[#9]

Thanks again.

[maxgro]

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.