Referencing visual cues in a straight table

[Anonymous]

Dear community,

I would like to add an expression to a straight table that checks whether the visual cue in another column of the same row is active or not.

All values below 80% are green. Can I add a column where QlikViews checks if the visual cue is active or not?

I can not reuse the expression with an if function like

If(fabs(Sum({$<[FS Item]={'1180000'}>}([Period Value GC]))) < 0.8, 'visual cue active')

as I am using "full accumulation" and "relative". The above expression only returns 'visual cue active' when the
actual value is smaller than 0.8 €, regardless of the accumulated percentage.

Thank you in advance.

Best regards,

Mats

[Anonymous]

I have tried to use the "Text Color" option instead of visual cues

If([DTA  Betrag kumuliert] < 0.8, LightGreen())

but that doesn't seem to work with  "relative" and "full accumulation" either.

[prieper]

Think that you cannot query for the status of the "visual cue", but for each expression you may enter further conditions for the background, text-color etc.

Just open the small + for the expression.

Peter

[prieper]

You may also repeat the entire calculation-condition of your formula.

Else please post an example

[Anonymous]

Dear Peter,

Thanks for your reply.

I should've clarified this in the original post. I've stumbled across the text-color option for expressions.

Unfortunately, it doesn't seem to work with "relative" and "full accumulation" (2nd Screenshot). 

I might just create an example then.

[Anonymous]

Here is an example.

I'd just like to add a column that says "1" or "active" where the values are green.

Thanks!

[prieper]

Accumulation might be expressed in a formula like

SUM(Expression1) + ABOVE(SUM(Expression1))

and then put your check on top of it,

eg IF(SUM(Expression1) + ABOVE(SUM(Expression1)) / SUM(TOTAL Expression1) > 0.8, LIGHTGREEN())

Peter

[Anonymous]

I've tried multiple versions of the expression you mentioned to build the sum of the column with absolute values.

It would be something like this:

IF(SUM(fabs(Sum({$<[FS Item]={'1180000'}>}([Period Value GC]))))

+ ABOVE(SUM(fabs(Sum({$<[FS Item]={'1180000'}>}([Period Value GC])))))

/ SUM(TOTAL fabs(Sum({$<[FS Item]={'1180000'}>}([Period Value GC]))))

> 0.8, LIGHTGREEN())

There seems to be a problem with the fabs() function though. That's why I was glad to find the inherent relative and full accumulation options. It's a nuisance that those options aren't recognized if you reuse the expression.

[Anonymous]

the problem ist the auto full accumulation option, just do it right:

if(  RangeSum(Above(TOTAL fabs(Sum({$<[Customer] = {'A'}>}Sales)),0,RowNo()))/Sum( TOTAL{$<[Customer] = {'A'}>}fabs(Sales))>0.8,1)

--> for the total expression you have to put the fabs() inside the sum

[Anonymous]

Hi Robin,

thank you for your suggestion! As I've mentioned here Fabs, Sum &amp; Set Expression just pulling the fabs() inside of the Sum function doesn't seem to work, unfortunately.

I used your expression. It doesn't return a value and for some reason the sorting (DTA Betrag descending) is not working anymore when I add the expression to the table.

Oh and don't get me wrong, I am sure I am just doing it wrong, I just thought there might be an easy way to address the visual cues as I've been struggling with the total expression for quite a while now.

Thanks again.

Mats