Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qlik Sense Count IF & Gauge Question

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

9 Replies
eduardo_sommer
Partner - Specialist
Partner - Specialist

Try the folloeing expression

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

Eduardo

Not applicable
Author

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
Master
Master

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
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
reddy-s
Master II
Master II

Hi Chris,

Try this expression:

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

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

Hi Chris,

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

Regards

Eduardo