Error in set modifier ad hoc element list: ',' or ')' expected in formula

[Anonymous]

Hello,

I am using the formula below but am getting the "Error in set modifier ad hoc element list: ',' or ')'.   The formula works about 90% of the time but shows this error message for a few certain customers.

Could someone please take a look at this formula and see if I need to revise it with a simple ',' or ')'.

=Sum({<BUSINESS_STATUS-={Completed}, PAY_TYPE={SDD},COUNTERPARTY_BANK_NAME={$(=Chr(34) & Concat({SMDD}Distinct COUNTERPARTY_BANK_NAME, Chr(34) & ',' & Chr(34)) & Chr(34))},

COUNTRY_NAME={$(=aggr(if(Rank(total Sum({<BUSINESS_STATUS-={Completed}, PAY_TYPE={SDD},  COUNTERPARTY_BANK_NAME={'$(=Chr(34) & Concat({SMDD}Distinct COUNTERPARTY_BANK_NAME, Chr(34) & ',' & Chr(34)) & Chr(34))}>}APPLREF))=4,COUNTRY_NAME),COUNTRY_NAME))}>}APPLREF)

Thanks

[swuehl]

Double check what each of your three dollar sign expansions with a leading equal sign (i.e. an expression) returns.

For example by putting the expression in a text box.

[swuehl]

I also think there is a unmatched single quote at the beginning of this field modifier:

=Sum({<BUSINESS_STATUS-={Completed}, PAY_TYPE={SDD},COUNTERPARTY_BANK_NAME={$(=Chr(34) & Concat({SMDD}Distinct COUNTERPARTY_BANK_NAME, Chr(34) & ',' & Chr(34)) & Chr(34))},

COUNTRY_NAME={$(=aggr(if(Rank(total Sum({<BUSINESS_STATUS-={Completed}, PAY_TYPE={SDD},  COUNTERPARTY_BANK_NAME={'$(=Chr(34) & Concat({SMDD}Distinct COUNTERPARTY_BANK_NAME, Chr(34) & ',' & Chr(34)) & Chr(34))}>}APPLREF))=4,COUNTRY_NAME),COUNTRY_NAME))}>}APPLREF)

Besides this, I don't think the aggr() function is useful here, I would use an advanced search instead:

=Sum(

{<BUSINESS_STATUS-={Completed}, PAY_TYPE={SDD},COUNTERPARTY_BANK_NAME= p({SMDD}),

COUNTRY_NAME={"=Rank(Sum({<BUSINESS_STATUS-={Completed}, PAY_TYPE={SDD},  COUNTERPARTY_BANK_NAME= p({SMDD}) >} APPLREF))=4"} >}APPLREF)

[Anonymous · Accepted solution]

Hi Stefan,

Thank you so much for the help.  The second example that you provided solved my issue. Works just like I need it to.

Thank you, Thank you Thank you.

[swuehl]

You're welcome.

If your question is answered, then please close this thread by flagging correct and / or helpful answers.

Qlik Community Tip: Marking Replies as Correct or Helpful