Calculating a count with certain conditions

[Anonymous]

I have the following tables:

P/T :

ID  CC.No   From Region   To Region   From PS   To  PS

1        123              A                   A                      X          H

2         345            B                   D                      Y          I

3         234            C                   E                       Z         Z

.

.

And another Table called ZH:

CC.No       Region       PSL

123              A               X

233              B               Y

455              C               Z

I want to create a measure that calculates the Count of ID with conditions :

If a Region is selected from the ZH table in the filters and From Region is not equal to To Region, then the measure value appears otherwise 0, and in the same code for the PSL as well.

A user can select only a PSL or Region at one time (Which I can put as a note). The 2 tables are connected by CC.No.

Any suggestions?

Thank You!

[Phil_Seamark]

HI @Anonymous

What is the direction of the relationship between the two tables?  Oh, and can you please post an expected result based on the sample dataset you have provided.

Cheers,

Phil

[Anonymous]

@Phil_Seamark 

Hey, it is in Single Direction.

The tables are:

The To CC No in the Prom/Trans is liked to the CC No in the ZH Table. (Many to One - Single Direction) 

I want to create a measure such that if the PSL or Region is a report  level filter:

1) Only a PSL or a Region can be selected one by one (When PSL is selected then user should not select a region- tjis part can be mentioned as a note to the end user)

2) The measure gives a  value only when a region or psl is selected

3)Whatever region or PSL is selected by the user, the measure should not include that value in From PSL or From Reg. For exacple if PSL U is selected, then this would link to TO PSL in Prom/Trans Table because of the link. so it should filter such that From PSL is NOT EQUAL to U, and calculate for the remaining.

Hope I was able to explain it clearly! Thanks! 

[v-lili6-msft]

hi, @Anonymous

the sample dataset you have provided is a little confusing. could you please share some sample data that is well convenient to create the relationship and calculate, as well as the expected output.

Do mask sensitive data before uploading.

Best Regards,

Lin

[Anonymous]

@v-lili6-msft

Hey, The Qlik View equivalent formula is as follows:

=if(getselectedcount(PSL_TEXT)>0,

count(DISTINCT(if( PRTR.FrPSL<>PRTR.ToPSL, PRTR.ID))),
 if(getselectedcount(REGION)>0,

count(DISTINCT(if( PRTR.FrRegion<>PRTR.ToRegion, PRTR.ID))),
 
 )

A user can choose a PSL or a Region only one at a time. When they select a PSL, this gets connected to TOPSL in the PROM TRANS Table (As the TO CC No. and CC No. are connected). It should return back the count where FR.PSL is not equal to ToPSL ( That is if ToPSL is A then FrPSL should be all others except A)

I hope this was clearer. Thanks!