Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mattphillip
Creator II
Creator II

Total qualifier multiple fields within angle brackets < >

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

1 Solution

Accepted Solutions
mattphillip
Creator II
Creator II
Author

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

View solution in original post

4 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
Creator II
Creator II
Author

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
Partner - Master III
Partner - Master III

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
Creator II
Creator II
Author

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