topic Re: Using a variable containing a text string value in an expression in a chart in QlikView

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

mattphillip — Sat, 16 Nov 2024 04:35:48 GMT

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

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

MindaugasBacius — Thu, 31 Jan 2019 14:10:49 GMT

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

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

mattphillip — Thu, 31 Jan 2019 14:15:16 GMT

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

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

pradosh_thakur — Thu, 31 Jan 2019 14:28:02 GMT

try this

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

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

mattphillip — Thu, 31 Jan 2019 14:30:44 GMT

Still the same result I'm afraid 😞

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

pradosh_thakur — Thu, 31 Jan 2019 14:31:51 GMT

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

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

mattphillip — Thu, 31 Jan 2019 14:35:40 GMT

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?

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

marcus_sommer — Thu, 31 Jan 2019 14:38:55 GMT

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

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

mattphillip — Thu, 31 Jan 2019 14:44:15 GMT

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

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

marcus_sommer — Thu, 31 Jan 2019 14:57:10 GMT

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

- Marcus