Total qualifier multiple fields within angle brackets < >

[mattphillip]

Hoping someone can help. I'm working on scripting an expression to calculation proportions within a pivot table. I know you can use the total qualifier as below to provide a sub-total related to a particular field. I also know you can have more than one field within the brackets. What I've done is allow the user to specify the field names and store them within a variable (as the pivot table acts as a custom report tool). This works fine when there are no spaces or special characters within the field names and you can have one or multiple field names. However, when there are spaces or special characters present, it will only accept a single field name with the variable also wrapped in double quotes e.g. "$(vDenomAppsOffers)".

Expression to populate vDenomAppsOffers (counts how many fields have been selected and then adds the chosen field names (which have been stored in the variables vNewFieldx). Where more than two fields are bring displayed in the table, the two right most field names are to be added.:

=if(vFieldCounter<2,'Session',if(vNewField4<>'Click to select a field',"vNewField3"&','&"vNewField4",if(vNewField3<>'Click to select a field',"vNewField2"&','&"vNewField3",if(vNewField2<>'Click to select a field',"vNewField2","vNewField1"))))

Proportion expression (counts all the conditional and unconditional offers and divides them by the total conditional and unconditional offers, subtotalled by the specified fields:

=count({$<[Decision]={'CONDITIONAL OFFER', 'UNCONDITIONAL OFFER'}>} [Applicant ID])/count(TOTAL <"$(vDenomAppsOffers)">{$<Decision={'CONDITIONAL OFFER', 'UNCONDITIONAL OFFER'}>} Decision)

Does anyone have an idea of what I might be doing wrong here?

Any help would be most appreciated.

Yours,

Mattt

[dplr-rn]

what is the output for $(vDenomAppsOffers)?
i would suggest if you can format it as [Field Name #1],[Field Name #2]
it should work without the quotes

[mattphillip]

Hi, vDenomAppsOffers is meant to display the field name (or names <field1>,<field2>). When I created a text object and made it equal to =vDenomAppsOffers it does this fine. But like I said, the table doesn't like fields with spaces or special characters. I tried adding the square brackets to the 'Set' button which the user clicks on to add their chosen field to the table (Action > Set Variable > vNewField1 =[$Field]) but that hasn't made any difference unfortunately.

M

[dplr-rn]

Dump the string output of vDenomAppsOffers  into a text box and share please.

I am assuming you used TOTAL <$(vDenomAppsOffers)>   (without quotes)

i did run a test locally and it worked

[mattphillip · Accepted solution]

I managed to figure it out. Turns out the brackets were in the wrong place. See below for the working code:

=count({$<[Response]={'FIRMLY ACCEPT OFFER'}>} [Applicant ID])/count(TOTAL <$(vDenomAppsOffers)>{$<[Response]={'FIRMLY ACCEPT OFFER'}>} Response)

Thanks for the assistance.

M