Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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)
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.
You're welcome.
If your question is answered, then please close this thread by flagging correct and / or helpful answers.