Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
AH
Contributor III

Possible and Excluded value calculation with filtering criteria

Hi,

I am trying to count the number of possible values and excluded values (based on filters criteria)  when i select values in another field. 

For example, If i select '500' in Column2 list box the possible value in the column1 list box is 'AB_14_0'. My goal is to find the distinct number of values in column1 where any value has a  '_14_' in it while its possible or excluded. _14_ is a dynamic value in the real data.

In my case when i select 500 there's total of 9 values in column1 that has _14_  in the value.

Thanks in advance!

 

Regards

Tags (1)
Labels (2)
1 Solution

Accepted Solutions
andrey_krylov
Valued Contributor

Re: Possible and Excluded value calculation with filtering criteria

Try to play around with min/max

=Count({1< Column1 = {"*_$(=Min(SubField(Column1, '_', 2)))_*"} > } Column1)

6 Replies
andrey_krylov
Valued Contributor

Re: Possible and Excluded value calculation with filtering criteria

Hi, not entirely clear what you need to get but try this

Count({1< Column1 = {"*$(=Mid(Column1, 3, 4))*"} >} Column1)

AH
Contributor III

Re: Possible and Excluded value calculation with filtering criteria

Hi Andrey_krylov,

 

Thank you for your replies. With a different mixture of values in column1, your formula dont seem to give the right values. Could you take a look?

 

Thanks in advance.

Regards,

AH

andrey_krylov
Valued Contributor

Re: Possible and Excluded value calculation with filtering criteria

Maybe this

Count({1< Column1 = {"*$(=Left(Right(Column1, 5), 4))*"} > } Column1)

or this

Count({1< Column1 = {"*_$(=SubField(Column1, '_', 2))_*"} > } Column1)

AH
Contributor III

Re: Possible and Excluded value calculation with filtering criteria

Hi Andrey_krylov,

Thank you very much again. Your solution is totally correct except i have a little nuances in the actual data. If by any chance Value2 has 1 value refer to 2 values in Column 1  then how the calculation would look like. 

For example, if i select 527 in Column2 then I would like to count only the values with _14_ not the _16_ which is a nuance in the data. There are other cases like this in the actual data. 

Is that possible?

 

Thanks in Advance.

Regards,

SH

andrey_krylov
Valued Contributor

Re: Possible and Excluded value calculation with filtering criteria

Try to play around with min/max

=Count({1< Column1 = {"*_$(=Min(SubField(Column1, '_', 2)))_*"} > } Column1)

AH
Contributor III

Re: Possible and Excluded value calculation with filtering criteria

Excellent! Thanks Andrey_krylov!!

 

Regards,

SH