Calculation with alternate states

[hendriks_steffe]

Hi Qlik Community,

I have a problem which I can’t figure out myself.

The data I have is like the following:

Article-ID            Selling Price        Catalogue

1111                      9,99                       A

1111                      8,99                       B

2222                      7,99                       A

3333                      7,99                       C

Furthermore I have 2 alternate states (CATALOGUE_1 and CATALOGUE_2) so that the user can choose 2 Catalogues from one dimension and compare those.

This works fine but now I need to calculate the overlap between the 2 alternate states.

For example when the user chooses Catalogue A and Catalogue B the overlap count would be 1 Article-ID because Article-ID 1111 is in both Catalogues.

I tried several things, but I am fairly new to calculate with alternate states:

sum(if(match( {CATALOGUE_1} Article_ID, {CATALOGUE_2} Article_ID),1 ,0))

count(distinct { CATALOGUE_1, CATALOGUE_2} Article_ID)

Edit 1: I tried count(distinct { CATALOGUE_1 * CATALOGUE_2} Article_ID) but I always get a 0 as return, although there are matching Article_IDs

Any help is greatly appreciated

Many thanks in advance

[sunny_talwar]

Try this

Count(DISTINCT {CATALOGUE_1 * CATALOGUE_2} Article_ID)

[hendriks_steffe]

Thanks for your help, but

count(distinct { CATALOGUE_1 * CATALOGUE_2}  Article_ID) always results in 0, although there are matching Article_IDs

[sunny_talwar]

How about this

Count(DISTINCT {<Article_ID = p({CATALOGUE_1}) * p(CATALOGUE_2)>} Article_ID)

[hendriks_steffe]

I tried

Count(DISTINCT {<Article_ID = p({CATALOGUE_1}) * p({CATALOGUE_2})>} Article_ID)

But this also results in 0 Values.

I am now using this:

if((count( { CATALOGUE_1 + CATALOGUE_2 < Article_Id = Article_Id>} Article_Id)) = 2, 1, 0) together with the overall-sum function.

This gives the right results but still feels clunky because I cannot calculate with it any further.

[sunny_talwar]

Would you be able to attach a sample to check this out?