Calculating a count with certain conditions

Question

I have the following tables:&nbsp;P/T :&nbsp;ID&nbsp; CC.No &nbsp; From Region&nbsp;&nbsp; To Region&nbsp;&nbsp; From PS&nbsp; &nbsp;To&nbsp; PS1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; H2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Z&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Z..&nbsp;And another Table called ZH:&nbsp;CC.No&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Region&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PSL123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; X233&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Y455&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Z&nbsp;I want to create a measure that calculates the Count of ID with conditions :&nbsp;If a Region is selected from the ZH table&nbsp;in the filters and From&nbsp;Region is not equal to To Region, then the measure value appears&nbsp;otherwise 0, and in the same code for the PSL as well.&nbsp;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.&nbsp;Any suggestions?&nbsp;Thank You!

phil_seamark · Answer

HI&nbsp;Anonymous
&nbsp;
What is the direction of the relationship between the two tables?&nbsp; Oh, and can you please post an expected result based on the sample dataset you have provided.
&nbsp;
Cheers,
&nbsp;
Phil

anonymous · Answer

Phil_Seamark&nbsp;&nbsp;Hey, it is in Single Direction.&nbsp;The tables are:&nbsp;The To CC No in the Prom/Trans is liked to the CC No in the ZH Table. (Many to One - Single Direction)&nbsp;&nbsp;I want to create a measure such that if the PSL or Region is a report&nbsp; level filter:&nbsp;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&nbsp; value only when a region or psl is selected3)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&nbsp;TO PSL&nbsp;in Prom/Trans Table because of the link. so&nbsp;it should filter such that From PSL is NOT EQUAL to U, and calculate for the remaining.&nbsp;Hope I was able to explain it clearly! Thanks!&nbsp;

v-lili6-msft · Answer

hi,&nbsp;Anonymousthe 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,&nbsp;as well as the expected output.&nbsp;Do mask sensitive data before uploading.&nbsp;&nbsp;&nbsp;Best Regards,Lin

anonymous · Answer

v-lili6-msft&nbsp;Hey, The Qlik View equivalent formula is as follows:&nbsp;=if(getselectedcount(PSL_TEXT)&gt;0,count(DISTINCT(if( PRTR.FrPSL&lt;&gt;PRTR.ToPSL, PRTR.ID))),&nbsp;if(getselectedcount(REGION)&gt;0,count(DISTINCT(if( PRTR.FrRegion&lt;&gt;PRTR.ToRegion, PRTR.ID))),&nbsp;&nbsp;)&nbsp;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)&nbsp;I hope this was clearer. Thanks!

Forum Discussion

Calculating a count with certain conditions

4 Replies

Related content

Conditional formatting on calculated item in calculation group

conditional Formatting in Calculation Item

Conditional formatting using multiple calculation groups

Calculated Measure with conditions

Average calculation with several conditions