Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
hendriks_steffe
Contributor II
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
sunny_talwar

Try this

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

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
Contributor II
Contributor II
Author

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?