topic Sumif function in QlikView

Sumif function

tim_leijen — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys!

Just trying to figure out the sumif equivalent in Qlikview.

From the forum I understood, it's =sum(if(.....

However I'm having some difficulties. So my aim is to get the total Net Contribution (NETCN) per account group.

I've followed all the steps on the forum, but for some reason the formula isn't working. Anyone has an idea?

=sum(if([Account Group], sum({<GL_1={NETCN},Dimension={ACT}>}Data)))

Re: Sumif function

tresB — Thu, 26 Jul 2018 06:22:00 GMT

Take a straight table:

Dimension: [Account Group]

Exp: sum({<GL_1={NETCN},Dimension={ACT}>}Data)

If this is not what you want. Try to share a sample data and explain expected output against that.

Re: Sumif function

tim_leijen — Thu, 26 Jul 2018 06:24:58 GMT

Ah sorry, I'm trying to set up a waterfall so it needs to be in a graph. After figuring out the sumif function in Qlikview I'll add another sumif behind it, getting a standard rate per bottle sold

=sum(if([Account Group], sum({<GL_1={NETCN},Dimension={ACT}>}Data))) /

sum(if([Account Group], sum({<GL_1={VOL},Dimension={ACT}>}Data)))

Re: Sumif function

tresB — Thu, 26 Jul 2018 06:28:44 GMT

What is your dimension in the chart? Could share your sample app?

Re: Sumif function

MindaugasBacius — Thu, 26 Jul 2018 06:33:01 GMT

Have you tried using:

Sum({<GL_1={'NETCN'},Dimension={'ACT'}>}Data) / Sum({<GL_1={'VOL'},Dimension={'ACT'}>}Data)

Re: Sumif function

tim_leijen — Thu, 26 Jul 2018 06:35:19 GMT

I have nothing yet, I'm trying to set up something similar as above

Re: Sumif function

thevingo — Thu, 26 Jul 2018 06:36:38 GMT

I think its best to deal it in the Script

sumif in scripts

Re: Sumif function

tresB — Thu, 26 Jul 2018 06:39:03 GMT

Try to share the excel file so that we can work on it to help you.

Re: Sumif function

tim_leijen — Thu, 26 Jul 2018 06:55:00 GMT

Trying to send an excel file here, but doesn't work... ill try to see if I can add the sumif formula in the script

Re: Sumif function

andrei_delta — Thu, 26 Jul 2018 07:10:08 GMT

Hi,

if you are trying to use "sum(if(" then the syntax is this: sum(if(condition,then, else))

in our case that syntax will be: sum(if( GL_1='NETCN' and DIMENSION='ACT', Data))

if the if-clause is true then it will give as result the values from the field "Data" and then it will be sum(Data).

the other examples you have been given already are set analysis

Hope it helps,

Andrew

Re: Sumif function

sunny_talwar — Thu, 26 Jul 2018 12:09:09 GMT

Look here to see how to share a sample

Uploading a Sample