topic Re: Calculation with alternate states in App Development

Calculation with alternate states

hendriks_steffe — Wed, 10 Apr 2019 11:41:39 GMT

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 — Wed, 10 Apr 2019 11:52:43 GMT

Try this

Count(DISTINCT {CATALOGUE_1 * CATALOGUE_2} Article_ID)

hendriks_steffe — Wed, 10 Apr 2019 11:55:41 GMT

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 — Wed, 10 Apr 2019 12:04:12 GMT

How about this

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

hendriks_steffe — Wed, 10 Apr 2019 12:28:18 GMT

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 — Wed, 10 Apr 2019 12:35:14 GMT

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