Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
sayadutt
Creator
Creator

Setting up external variable for chart expression

Hi Team,

Currently I have an expression in straight chart like :

if([Practice Area] = 'XXX' and $(vDateVar) > 5, RGB(245, 54, 83),

if([Practice Area] = 'YYY' and $(vDateVar) > 3,RGB(245, 54, 83),

red()))

Now my requirement is to get rid of the hard coded values (5 & 3 - marked in red), and read it from an external file, may be csv.

My csv will look like this.

[Practice Area], value

XXX, 3

YYY, 2


So the qvw should read this csv, pick up the corresponding value based on Practice Area and create the expression.

In this way if I modify the values in csv the same will be reflected in qvw without touching the chart expression.


Can you please guide me how I can read the values from an external file and use it in expression.



Thanks

9 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this.

if([Practice Area] = 'XXX' and $(vDateVar) > only({<[Practice Area] = {"XXX"}>}Value), RGB(245, 54, 83),

if([Practice Area] = 'YYY' and $(vDateVar) > only({<[Practice Area] = {"YYY"}>}Value),RGB(245, 54, 83),

red()))

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sayadutt
Creator
Creator
Author

Hello Kaushik,

Did not get this.

From where are we reading the values from external file in your example?

don't we need to read those values first, store it somewhere and then use it in expression?

Thanks

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

You must have loaded that table in script?

If not then you need to load that data in Application.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sayadutt
Creator
Creator
Author

Hi Kaushik,

I tried, but it seems not working. I cant see the RGB colors at all.Its not even going to the else part.

csv file:

Practice Area,Threshold

XXX,2

YYY,3

Load statement:

LOAD [Practice Area],

     Threshold

FROM

[path]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Expression in Background Color section:

if([Practice Area] = 'XXX' and $(vDateVar) > only({<[Practice Area] = {"XXX"}>}Threshold), RGB(245, 54, 83),

if([Practice Area] = 'YYY' and $(vDateVar) > (only({<[Practice Area] = {"YYY"}>}Threshold)),RGB(245, 54, 83),

RGB(0,0,0)))

it returns white colour (default colour), its not even returning the else part.

Is the above expression correct? Is it actually going inside the if part at all?

Please suggest.

Thanks

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Please share a sample file

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable

Could you share the script where you load the value into your variable vDateVar ?

For testing what happens if you hard code the value thus replacing $(vDateVar) ?

sayadutt
Creator
Creator
Author

Hi Kaushik,

I have tried to explain my complete requirement in the edit script section. Please have a look.

Attaching the sample.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

Here the problem was not expression, but it was of the variable you had created.

I have made changes to script.

Have a look at the attached application and give your feedback.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sayadutt
Creator
Creator
Author

Hi Kaushik,

Yes, the background colour portion is now working as expected.

Can you please suggest on the 2nd part I mentioned in the application.

// So, XXX 123 practice area & application, the day difference is 2 days and Threshold is 3 days , so it will be Amber (within limit)

// but for XXX 456 the threshold is 1 day ( it falls under 2 row of 2nd load statement.  XXX-123 threshold is 3 days . For XXX-All(anything other than 123), threshold is 1 day

//The reason for having All is.... threshold for 1 application is different from others belonging to the same Practice Area

// XXX, 123, 3

// XXX,        All, 1

// So the color will be Red. Date difference is coming as 5 days

There can be different threshold values for the same Practice Area (but different Applications). How to handle that?

For example, in the 1st Load statement

XXX has 3 applications 123,456,789

Now the threshold value for 123 is 3 days, but for remaining All (456&789) its 1 day.

How to handle that in the expression?

Thanks