topic Re: Sumif and Rangesum Question in QlikView

Sumif and Rangesum Question

Wed, 10 Sep 2014 05:37:26 GMT

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!

Re: Sumif and Rangesum Question

ecolomer — Wed, 10 Sep 2014 06:21:19 GMT

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

Re: Sumif and Rangesum Question

Wed, 10 Sep 2014 07:20:42 GMT

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

it says error in expression

Re: Sumif and Rangesum Question

ecolomer — Wed, 10 Sep 2014 10:52:42 GMT

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

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

Re: Sumif and Rangesum Question

maxgro — Wed, 10 Sep 2014 11:07:38 GMT

for max year

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

for 2014

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

for max year

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

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

for 2013 replace with 2013

Re: Sumif and Rangesum Question

ecolomer — Wed, 10 Sep 2014 12:16:55 GMT

See solution from Massimo, is more efficient with Set Analysis

Re: Sumif and Rangesum Question

ashwanin — Wed, 10 Sep 2014 12:32:41 GMT

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.

Re: Sumif and Rangesum Question

Wed, 10 Sep 2014 15:22:22 GMT

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

Re: Sumif and Rangesum Question

Wed, 10 Sep 2014 15:22:41 GMT

Thanks again.

Re: Sumif and Rangesum Question

maxgro — Wed, 10 Sep 2014 15:50:29 GMT

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.