topic Re: Sum If in QlikView

Sum If

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

I have attached excel example. I am trying to get a sumif function in set analysis to sum $amount for acct # '=IF((+G2+H2)=0,(E2+F2),IF((E2+F2+H2)<>0,-G2,0))

Can someone help on the formula I need?

Thanks,

Josh

Re: Sum If

Arthur_Fong — Fri, 06 Sep 2019 00:51:43 GMT

Try this in your expression:

if(
sum({<Account*={'201110','507200'}>}Value)=0,
sum({<Account*={'121100','122100'}>}Value),
if(sum({<Account*={'121100','122100','507200'}>}Value)<>0,
sum({<Account={'201110'}>}Value)*-1,0
))

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

Re: Sum If

Brett_Bleess — Thu, 19 Sep 2019 12:19:59 GMT

Josh, did Arthur's post/example get you what you needed? If so, please use the Accept as Solution button on his post to give him credit for the help and to let others know his example worked for your issue. If you are still trying to figure things out, please provide an update and any further information you can share.

Regards,
Brett

Re: Sum If

jmcdermott — Wed, 25 Sep 2019 15:06:43 GMT

My 3 month data set is 86k records, Arthur's formula is only working for me if if I keep the detail and do a pivot in excel. If I collapse the records to affiliate code level like I want then the numbers aren't correct. Not sure how to fix that?

Re: Sum If

Brett_Bleess — Wed, 25 Sep 2019 21:17:00 GMT

Josh, would you be able to attach your app for Arthur? That is likely what he would need at this point in order to try to help you get things the rest of the way is my hunch, so if you can, let us know, may not need the entire thing, just a subset of data etc. in order to be able to see the data model properly and the chart and expressions etc. Sorry I am not more help on this one.

Regards,
Brett

Re: Sum If

jmcdermott — Thu, 03 Oct 2019 17:50:21 GMT

Good afternoon Arthur,

I tried adding one more account to the equation and am getting a "Error in the set modifier as hoc element list" message.

if(sum({<acct*={'201110','507200'}>}dom_amount)=0,
sum({<acct*={'121100','122100',}>}dom_amount),
if(sum({<acct*={'121100','122100','507200'}>}dom_amount)<>0,
sum({<acct={'201110'}>}dom_amount)*-1,
if(sum({<acct={'201150'}>}dom_amount)<>0,
sum({<acct={'201150'}>}dom_amount)*-1,0
)))

Do you see the issue, or is it we can only have 2 if(sum)'s in the equation and not 3?

Re: Sum If

Brett_Bleess — Thu, 03 Oct 2019 18:31:42 GMT

Josh, apologies, but I am wondering if you by chance had a look at the Help at all, it is pretty robust on Set Analysis, and I am going to include another Design Blog post as well, but if @Arthur_Fong Arthur has a chance too to have a look, that would be great, but I wanted to try to give you something that might help here.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Note, there are some sublinks under the above link too, that may be where the info you truly want is located....

Here is the Design Blog link, and there are some associated links at the bottom of the post, just FYI, hopefully one of these may help you find things too, I am terrible at Set Analysis unfortunately.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Regards,
Brett

Re: Sum If

Arthur_Fong — Fri, 04 Oct 2019 01:59:21 GMT

The error occurs because there is an extra ',' in your expression(second line in bold):

Remove that ',' and it should work.

Thanks and regards,

Arthur Fong