Sumifs function in qlikview

[jyothish8807]

Hi,

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

Regards

KC

[tresesco]

May be like:

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

[jyothish8807]

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

KC

[tresesco]

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.

[jyothish8807]

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

KC

[amit_saini]

KC

Try this :

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

Thanks,
AS

[jyothish8807]

Not working Amit, Thanks for the help

Regards

KC

[tresesco · Accepted solution]

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

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

PFA

[jyothish8807]

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

KC

[anbu1984]

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;