Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

display values with a specific condition

Hello got the following sample data
Payment ID    Payment Source    Payment Amount
1                         Source 1                     500
1                         Source 1                     400
1                         Source 2                     900
2                         Source 1                     100
2                         Source 2                      50

I want to calculate the variance of each Payment ID between Source 1 and Source 2
Variance is:
Sum({< [Payment Source] = {"Source 1"}>} Payment Amount) - Sum({< [Payment Source] = {"Source 2"}>} Payment Amount) 

Now what I want to display on a Table Chart the payments that got a variance = 0
so in this table I want to display only Payment ID 1

kindly advise on how to accomplish this

I can walk on water when it freezes
Labels (1)
1 Solution

Accepted Solutions
eddie_wagt
Partner - Creator III
Partner - Creator III

Ok, you have two options here.

1. Calculate this in the loadscript (allways better)

2. Use the calculation as calculated dimension and for the measure (you'll need it anyway) you fill in =1 and then for conditional show you fill in 0. See example (is Dutch)

eddie_wagt_0-1653665905635.png

 

 

View solution in original post

5 Replies
Andrei_Cusnir
Specialist
Specialist

Hello,

 

You can use something similar to this expression:

Sum(Aggr(Sum({<[Payment Source]={"Source 1"}>}[Payment Amount]), [Payment Source], [Payment ID]))

 

The dataset looks like this:

 

Then you can create a Table chart with [Payment ID] dimension and the expression above as measure, which will give you an output:

 

As you can see the ID 1 is 0 and ID 2 is 50. So you can go a step further, by adding the expression in an If() statement to display the value of ID if it is 0 or Null() and it will remove all the rows that are not 0.

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
eddie_wagt
Partner - Creator III
Partner - Creator III

If I understood your requirement correctly, you want to show payments where the balance is null. You can use your expression as a measure, but you will need a calculated dimension to show only the payments with balance is null.

You can use this as a calculated dimension (use the expression editor for this field)

 

=Aggr(If(Sum({< [Payment Source] = {"Source 1"}>} [Payment Amount]) - Sum({< [Payment Source] = {"Source 2"}>} [Payment Amount]) =0,[Payment ID]),[Payment ID])

 

Now you only have to untick the box 'Null-values'

eddie_wagt_0-1653643533300.png

 

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.
ali_hijazi
Partner - Master II
Partner - Master II
Author

Hello
I managed to do the following; the thing is I only want to display a dimension i.e. without a measure
aggr(only
(
{
<[Payment ID]={
"=sum(aggr(Sum({<[Payment Source] = {[Source1]}>}[Payment Amount]) - Sum({<[Payment Source] = {[Source2]}>}[Payment Amount]),[Payment ID])) = 0"}
>
}[Payment ID]),[Payment ID])

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II
Author

Hello
I managed to do the following; the thing is I only want to display a dimension i.e. without a measure
aggr(only
(
{
<[Payment ID]={
"=sum(aggr(Sum({<[Payment Source] = {[Source1]}>}[Payment Amount]) - Sum({<[Payment Source] = {[Source2]}>}[Payment Amount]),[Payment ID])) = 0"}
>
}[Payment ID]),[Payment ID])

I can walk on water when it freezes
eddie_wagt
Partner - Creator III
Partner - Creator III

Ok, you have two options here.

1. Calculate this in the loadscript (allways better)

2. Use the calculation as calculated dimension and for the measure (you'll need it anyway) you fill in =1 and then for conditional show you fill in 0. See example (is Dutch)

eddie_wagt_0-1653665905635.png