Qlik Sense Count IF & Gauge Question

[#1]

Hi All,

I'm new to Qlik so apologies if this is a silly question.

I currently have a table that contains a column which is the difference between the 2 preceeding columns:

ID    |     A    |    B    |    B-A

102  |    10    |    10   |    0

103  |    10    |    5     |    5

I'm trying to implement a gauge which shows the percentage of records in this table where the final column = 0 but am struggling with the expression to calculate this. I should also mention that columns A and B are SUM functions based on the ID dimension.

Thanks in advance,

Chris

[eduardo_sommer]

Try the folloeing expression

=count (${<[B-A]={0}>} ID)

Eduardo

[#3]

I'm a bit confused though. If I have created the table as a visualization in a sheet, then how do I access the values in that table from another visualization (such as a gauge)?

[brunobertels]

Hi

If you want a percentage of the number of value egual to Zero in column B-A (number of values = 0 / total value in column B-A)

Try this :

=count (${<[B-A]={0}>} ID) / count(distinct(ID)

Regards

Bruno

[eduardo_sommer]

Hi,

You don't access the values from another visualization, but you can simulate a table, inside your gauge, using the aggr() function.

Explain the outcome you need and I'll try to help you.

Eduardo

Editing: the suggestion by Bruno, below gives you the answer

[#6]

Hi Eduardo,

Let's say I have 2 tables

ID    |    A Value

1      |    10

1      |    20

2      |    7

2      |    5

ID    |    B Value

1      |    30

2      |    8

2      |    10

In my current visualization i've taken these and created a third table C (as in original post)

Columns A and B in Table C summarize the values in each table by ID and then the final column finds the difference. In the example here the output would be:

ID     |     A    |    B    |    B-A

1      |    30    |    30   |    0

2      |    12    |    18     |    6

I'm then trying to create a gauge to say for what percentage of the ID's would the final column be 0.

I hope that makes sense...

Thanks again for all your help!

Chris

[jonathandienst]

The gauge expression for the percentage should be:

Count(Aggr(If(RangeSum(B, -A) = 0, ID), ID)) / Count(distinct ID)

Set the gauge limits to 0 and 1.

[reddy-s]

Hi Chris,

Try this expression:

count (${<[B-A]={0}>} ID) / count(ID)

[#9]

Thanks to everyone for their help. I've solved my problem. In the end I had to use the aggr() function as suggested by others. The final expression ended up being very messy and i'm sure there is a neater way to do it:

count(IF(aggr(aggr(SUM(B), ID) - aggr(SUM(A), ID), ID) = '0.00', ID)) / count(aggr(aggr(SUM(B), ID) - aggr(SUM(A), ID), ID))

[eduardo_sommer]

Hi Chris,

Use the expression that jontydkpi, below, suggested. This will give you the expected result.

Regards

Eduardo