Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
hendriks_steffe
New Contributor II

Calculation with alternate states

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

Labels (2)
5 Replies

Re: Calculation with alternate states

Try this

Count(DISTINCT {CATALOGUE_1 * CATALOGUE_2} Article_ID)
hendriks_steffe
New Contributor II

Re: Calculation with alternate states

Thanks for your help, but

 

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

Re: Calculation with alternate states

How about this

Count(DISTINCT {<Article_ID = p({CATALOGUE_1}) * p(CATALOGUE_2)>} Article_ID)
hendriks_steffe
New Contributor II

Re: Calculation with alternate states

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.

 

 

Re: Calculation with alternate states

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