# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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.

Regards

Tags (1)
Labels (2)

• ### Possible values

1 Solution

Accepted Solutions
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
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)

Contributor III

## Re: Possible and Excluded value calculation with filtering criteria

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?

Regards,

AH

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)

Contributor III

## Re: Possible and Excluded value calculation with filtering criteria

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?

Regards,

SH

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)

Contributor III

## Re: Possible and Excluded value calculation with filtering criteria

Excellent! Thanks

Regards,

SH