Using a variable containing a text string value in an expression in a chart

[mattphillip]

Hoping someone can help. I've got a variable which is populated by a text string (department name containing a comma) upon a click of a button. This variable is then fed into a chart expression to customise a bar chart by specifying the department held in the Department field. Whilst this worked when the abbreviated names were used (with no spaces or commas), now it doesn't even though single quotes have been included. Please see below.

Expression in Chart:

=count({<Department={$(vDisplayDepartment)}>} [Staff ID])

Department Names stored in variables vDepartment1, vDepartment2 and vDepartment3 via the Document Settings > Variables Tab and are populated in the following format.  ='Department of Business, Law and Communication'  

Upon the click of the department button the variable vDisplayDepartment=vDepartment1 (the latter determined upon which button is clicked.

Like I said however, when I click the button the chart simply states there is no data to display.

Can anyone help?

Appreciated.

Matt

[MindaugasBacius]

Why not trying something like this:
=count({<Department={'$(=$(vDisplayDepartment))'}>} [Staff ID])

[mattphillip]

Thanks for the idea. I tried it but got the same result unfortunately.

[pradosh_thakur]

try this

=count({<Department={$(=vDisplayDepartment)}>} [Staff ID])

[mattphillip]

Still the same result I'm afraid 😞

[pradosh_thakur]

You may want to include the string in single quotes. for example 'abc', 'def','ghi' if spaces etc are included.

[mattphillip]

I've specified the variable string values via the Settings>Document Properties>Variables tab as follows:

='Department of Business, Law and Communication'

Am I missing something else?

[marcus_sommer · Accepted solution]

I think both:

vDisplayDepartment=vDepartment1
   and
{$(vDisplayDepartment)}

will fail because of missing quotes and/or spaces in the content.

I suggest to remove this logic and to use just selections within the field Department and should there really a good reason of using a second source to select the values to switch the variable/button approach to an additionally field-selection like:

ExtraDepartment:
load * inline [
ExtraDepartment
Dep1
Dep2
...
];

and then within the expression:

count({<Department=p(ExtraDepartment)>} [Staff ID])

- Marcus

[mattphillip]

Thanks Marcus, I'll try that. Can you confirm what the p after the '=' is used for? Just trying to understand things a bit more.
Thanks,
Matt

[marcus_sommer]

p() and e() are special set analysis functions which return the possible respectively the excluded field-values of the specified field.

- Marcus