topic Sum of values that follow some rules in App Development

Sum of values that follow some rules

caiogil1 — Tue, 18 Jul 2017 17:11:18 GMT

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.

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 = '#'.

caiogil1 — Tue, 18 Jul 2017 17:23:05 GMT

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.

sunny_talwar — Tue, 18 Jul 2017 17:46:19 GMT

May be this

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

sunny_talwar — Tue, 18 Jul 2017 17:48:26 GMT

Sample qvw and image attached

Anonymous — Tue, 18 Jul 2017 17:58:17 GMT

This works for me:

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