Sum If

[jmcdermott]

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

[Arthur_Fong · Accepted solution]

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

[Brett_Bleess]

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

[jmcdermott]

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?

[Brett_Bleess]

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

[jmcdermott]

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?

[Brett_Bleess]

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/...

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

[Arthur_Fong · Accepted solution]

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

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
)))

Remove that ',' and it should work.

Thanks and regards,

Arthur Fong