topic Re: Possible and Excluded value calculation with filtering criteria in QlikView

Possible and Excluded value calculation with filtering criteria

AH — Sat, 16 Nov 2024 05:09:20 GMT

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

Re: Possible and Excluded value calculation with filtering criteria

andrey_krylov — Fri, 23 Nov 2018 05:01:13 GMT

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

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

Re: Possible and Excluded value calculation with filtering criteria

AH — Fri, 23 Nov 2018 16:24:46 GMT

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,

Re: Possible and Excluded value calculation with filtering criteria

andrey_krylov — Fri, 23 Nov 2018 16:57:50 GMT

Maybe this

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

or this

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

Re: Possible and Excluded value calculation with filtering criteria

AH — Fri, 23 Nov 2018 17:40:50 GMT

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,

Re: Possible and Excluded value calculation with filtering criteria

andrey_krylov — Fri, 23 Nov 2018 18:17:38 GMT

Try to play around with min/max

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

Re: Possible and Excluded value calculation with filtering criteria

AH — Fri, 23 Nov 2018 18:30:40 GMT

Excellent! Thanks Andrey_krylov!!

Regards,