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: 
caiogil1
Contributor III
Contributor III

Sum of values that follow some rules

Hello,

I have a problem here and I am wondering how to make this works on Qlik Sense.

So, I have this table here to use as example.

 

Col1ProductCol3Col4
xa20
xb3#
ya7#
yc8#

What I need from this data is the sum of Col3 where Product has Col1 = 'X' AND 'Y' (Both).

Also this sum of Col3 have to consider just rows where Col4 = '#'.


So the result on this example will be '7'. Since only Product 'A' has the requirements and the 1st row is not Col4 = '#'.

I hope to learn a lot here and help if I can
4 Replies
caiogil1
Contributor III
Contributor III
Author

Sum({<Col2 = P({$<Col1={'x'}>}Col2) * P({$<Col1={'y'}>}Col2), Col4-={0}>}Col3)

Even that this formula gives me the expected value, I was wondering if is really correct.

I am new using indirect set analysis and not sure if this case is ok to use that.


If someone has another alternative or some adjustment to the formula that I used, will be very welcome.

I hope to learn a lot here and help if I can
sunny_talwar

May be this

Sum({<Product = {"=Count(DISTINCT {<Col1 = {'x', 'y'}>} Col1) = 2"}, Col4 = {'#'}>}Col3)

sunny_talwar

Sample qvw and image attached

Capture.PNG

Anonymous
Not applicable

This works for me:

sum(if(aggr(nodistinct concat(Col1),Product)='xy' and Col4='#', Col3))