if vs set analysis different results

[Anonymous]

Hi all,

I have a different result using set analysis (with intersection) and if functions.

When i select a field used in set analisys with intersection : all formulas that test the value i selected are correct, formulas with the other possible value (expected 0 because of interesection) no.

For example (see the attachment):

Fact_Table:

LOAD * INLINE [

    %k_customer, flag, aggr

    1, s, s

    2, s, s

    2, n, n

];

customer:

LOAD * INLINE [

    %k_customer,name, cnt

    1, one,1

    2, two,1

];

when i select flag =n

sum({$*<flag={'s'}>}cnt)    = 0

sum(if(flag='s',cnt))          = 1

I was expecting 0 because of intersection in setanlaysis. I saw this happen because the value to sum is in another table and the key has both values (s and n)

what's wrong? Do i have to replace all formula with if instead of set analysis ? or create another field with the same value just for selection?

thanks all

[Anonymous]

have a look here:

https://community.qlik.com/message/1020320#1020320

[Anonymous]

right!

I can't replace set analysis with if because of possible duplication (I used sum to avoid count(distinct)).

So Options are:

1)use count(distinct (name))

2)create another field with the same value just for selection

[Anonymous]

correct, I think many people aren't aware of the temporary join tables...

[Anonymous]

another idea:

sum({<(flag={"=flag='s'"}>} cnt))

[Anonymous · Accepted solution]

This is a  really good idea!!!!

Thanks a lot Robin

worked without () 

sum({<flag={"=flag='s'"}>} cnt)

[Anonymous]

... of course without the parentheses ...