Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
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
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

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