topic Re: Referencing visual cues in a straight table in QlikView

Referencing visual cues in a straight table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Referencing visual cues in a straight table

Anonymous — Mon, 29 May 2017 14:28:41 GMT

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.

Re: Referencing visual cues in a straight table

prieper — Mon, 29 May 2017 14:29:11 GMT

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

Re: Referencing visual cues in a straight table

prieper — Mon, 29 May 2017 14:31:00 GMT

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

Else please post an example

Re: Referencing visual cues in a straight table

Anonymous — Mon, 29 May 2017 14:43:30 GMT

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.

Re: Referencing visual cues in a straight table

Anonymous — Mon, 29 May 2017 14:51:08 GMT

Here is an example.

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

Thanks!

Re: Referencing visual cues in a straight table

prieper — Mon, 29 May 2017 14:51:30 GMT

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

Re: Referencing visual cues in a straight table

Anonymous — Mon, 29 May 2017 15:12:18 GMT

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.

Re: Referencing visual cues in a straight table

Anonymous — Mon, 29 May 2017 16:34:32 GMT

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

Re: Referencing visual cues in a straight table

Anonymous — Mon, 29 May 2017 17:06:42 GMT

Hi Robin,

thank you for your suggestion! As I've mentioned here Fabs, Sum & 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

Re: Referencing visual cues in a straight table

Anonymous — Mon, 29 May 2017 17:31:11 GMT

Hi Mats,

just create a better example, with all the fields that are used as dimensions and within expressions....

Preparing examples for Upload - Reduction and Data Scrambling

--> you can scramble confident fields or replace them manually with random data...

Regards Robin

Re: Referencing visual cues in a straight table

Anonymous — Tue, 30 May 2017 07:33:18 GMT

The data reduction and scramble tools are quite effective, I thought it would take far longer to adjust the file.

Please find it attached.

Re: Referencing visual cues in a straight table

prieper — Tue, 30 May 2017 07:45:33 GMT

Should be like the attached?

Peter

Re: Referencing visual cues in a straight table

Anonymous — Tue, 30 May 2017 08:12:27 GMT

I would like to mark every company that is within the 95% range (or any other percentage, determined by the variable in the input box) with "1" or similar.

As you can see there are 6 accounts (tables in the container object) in total, I would like to first assign the value (e.g. 1) so that I can build a sum column. The insight the user is looking for can be described as

"If I audit Company A, how much of the deferred tax assets, current income tax receivables etc. is covered?"

You want to audit only those companies that are in the chosen %range in as many of the 6 accounts/categories as possible.

The final (total) table should provide information like

Company Number of categories(accounts) where the company is within %range

A 5

C 3

I hope that makes sense.

Thank you in advance.

Mats

Re: Referencing visual cues in a straight table

Anonymous — Tue, 30 May 2017 08:14:06 GMT

So no matter if I use visual cues or the text color option, in the end I want to count the amount of times the company is within the chosen %range.

Re: Referencing visual cues in a straight table

prieper — Tue, 30 May 2017 08:22:55 GMT

Dann gebe doch bitte mal ein Beispiel mit input und gewünschtem output.

Re: Referencing visual cues in a straight table

Anonymous — Tue, 30 May 2017 09:02:49 GMT

Das wäre als Output der Hilfsspalte vollkommen ausreichend. So könnte ich nachher die Hilfsspalten aufsummieren

und die Aussage treffen: Company XY ist in 4/6 Fällen in der gewählten Range.

Re: Referencing visual cues in a straight table

prieper — Tue, 30 May 2017 09:27:13 GMT

so in etwa?

Re: Referencing visual cues in a straight table

Frank_Hartmann — Tue, 30 May 2017 09:29:22 GMT

versuche es mal mit dieser Formel:

If(RangeSum(Above( (Sum({$<[FS Item] = {'1180000'}>}[Period Value GC])),0,RowNo()))/

Sum( TOTAL{$<[FS Item] = {'1180000'}>}([Period Value GC]))<=0.95,1,0)

Re: Referencing visual cues in a straight table

Anonymous — Tue, 30 May 2017 09:44:04 GMT

Hallo Peter,

ja, das geht genau in die richtige Richtung!

Das einzige verbleibende Problem ist, dass er mir die Sortierung verhaut, wenn ich die neuen Funktionen verwende (DTA Betrag soll ja absteigend sortiert sein). Hat jemand eine Ahnung, woran das liegen könnte? Auf dem Sortieren-Tab habe ich alle anderen Optionen entfernt.