topic Sumifs function in qlikview in QlikView

Sumifs function in qlikview

jyothish8807 — Tue, 30 Sep 2014 09:55:36 GMT

Hi,

How to create a sumifs (Excel function) function in QV?

Regards

Re: Sumifs function in qlikview

tresB — Tue, 30 Sep 2014 09:59:17 GMT

May be like:

=Sum(If(Value>5, Value))

Re: Sumifs function in qlikview

jyothish8807 — Tue, 30 Sep 2014 10:08:58 GMT

Hi Tresco,

Above one is Sumif function.My requirement is as follows:

=SUMIFS($J$4:$J$18,$B$4:$B$18,A16)

Sum of field J , where ever value of A16 cell is present in B field.

Hope you understand my requirment.

Regards

Re: Sumifs function in qlikview

tresB — Tue, 30 Sep 2014 10:11:27 GMT

Do you want this output in a textbox? There would some more questions to understand your requirement properly. Better create a sample qvw and share explaining your expected output.

Re: Sumifs function in qlikview

jyothish8807 — Tue, 30 Sep 2014 10:19:17 GMT

I want the output in staright chart in a custom field.

I have three fields A,B,C.

D is my custom field.

A	B	C	D(o/p)
1	1	2	7
2	1	2	0
1	1	3	7
3	3	2	2

Over here when A=1, we check where we have 1 in B field and take the sum of C field where ever i have 1 in B field.

Regards

Re: Sumifs function in qlikview

amit_saini — Tue, 30 Sep 2014 10:47:48 GMT

Try this :

aggr( sum(total If(a=c,c)),b,c)

Thanks,
AS

Re: Sumifs function in qlikview

jyothish8807 — Tue, 30 Sep 2014 11:28:26 GMT

Not working Amit, Thanks for the help

Regards

Re: Re: Sumifs function in qlikview

tresB — Tue, 30 Sep 2014 11:52:34 GMT

Finally got !(I believe); was a bit tricky.

=If(A=Aggr(NODISTINCT B,B),Sum(total <B> C))

PFA

Re: Sumifs function in qlikview

jyothish8807 — Tue, 30 Sep 2014 16:27:39 GMT

This is awesome tresesco , but now iam not getting 2 (in field A) in my chart.Is there a way to display that also?

I almost got screwed with this expression .

Can you explain this part also, will be really helpful.

(Sum(total <B> C)

Regards

Re: Sumifs function in qlikview

anbu1984 — Tue, 30 Sep 2014 17:49:44 GMT

In script

Initial:

Load * Inline [

A,B,C

1,1,2

2,1,2

1,1,3

3,3,2];

Left Join(Initial)

Load B As A,Sum(C) as D Resident Initial Group by B;

Re: Sumifs function in qlikview

jyothish8807 — Wed, 01 Oct 2014 04:33:45 GMT

Hi tresesco,

In your app when i take a,b.c iam getting all values of A (1,2,1,3)

But when i make the same chart iam getting only A (1,1,3) Iam not getting the entries of 2.Why is it so? Whatds the trick?

Regards

Re: Re: Sumifs function in qlikview

tresB — Wed, 01 Oct 2014 04:52:21 GMT

If you want the missing one, try:

If(A=Aggr(NODISTINCT B,B),Sum( total <B>C),0)

and then in presentation tab uncheck 'Supress Zero-Values'.

Re: Sumifs function in qlikview

jyothish8807 — Wed, 01 Oct 2014 04:55:49 GMT

Got it.Thaks a lot.Its working with the previous expression also.It was supress Zero issue.

Regards