Coloring my column nicely according to a sum() without too much calculations

[lucas3]

Hello,

I made a pivot table that displays some sums in every cells of every columns

Now I would like to color it nicely, for example:

if value < -100000, strong red
if -100000<=value<-50000, red
if -50000<=value<0, light red

etc.... (I would have around 7 or 9 conditions)

Problem is that value in my expression right above is a sum calculation, so this kind of syntax is not good because the same calculation would be made many times.

Is it possible to declare a variable inside an expression or is it only allowed in scripts ?
If so, can you guys recommend a method please ?

Thank you very much

Lucas

[chrismarlow · Accepted solution]

Hi,

While I understand where you are coming from have you seen this post on caching?

The QlikView Cache - Qlik Community - 1468270

Cheers,

Chris.

[lucas3]

This is amazing, thank you. Do you have further readings to recommend me about Qlik caching please ? It caught my attention

But this means I will still need to re-write my formula though ? No way to give it a simple name ?

Thank you for your help

[chrismarlow · Accepted solution]

Hi,

I think you can use a variable and then '$()'.

So in your script (or possibly through document properties)

let vFormula='sum([really long formula thing])';

Then in your chart something like (not tested, you will need more brackets and commas I am sure);

if $(vFormula) < -100000, strong red
if -100000<=$(vFormula) and $(vFormula)<-50000, red
if -50000<=$(vFormula)<0, light red

Cheers,

Chris.

[lucas3]

Amazing, thank you very much 🙂