Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator 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

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Try to play around with min/max

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

View solution in original post

6 Replies
Highlighted
Specialist
Specialist

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

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

Highlighted
Creator III
Creator III

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

Highlighted
Specialist
Specialist

Maybe this

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

or this

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

Highlighted
Creator III
Creator III

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

Highlighted
Specialist
Specialist

Try to play around with min/max

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

View solution in original post

Highlighted
Creator III
Creator III

Excellent! Thanks Andrey_krylov!!

 

Regards,

SH