topic Re: Re: Re: Filtering using expression in QlikView

Filtering using expression

chiso_chiso — Mon, 13 Oct 2014 09:29:43 GMT

I have a crosstable with a fields called Question and Answer. I want to filter only questions with a specific word and count the number of responses.

Example, I want Overall Availability, Coke Availability, 20l Coke Availability, 40l Coke Availability seperately.

See Attached

Re: Filtering using expression

jagan — Mon, 13 Oct 2014 09:49:20 GMT

Hi,

Try like this using wild card characters in set analysis

=Count({<Question={'*Availability*'}>} Answer)

Regards,

jagan.

Re: Filtering using expression

Colin-Albert — Mon, 13 Oct 2014 09:52:38 GMT

You may need to load the question/answer data as uppercase or lowercase to ensure that you count all values of a word if the case varies.

Re: Filtering using expression

chiso_chiso — Mon, 13 Oct 2014 09:52:43 GMT

Is it possible to also filter for 'Yes' answers only as in to say Overall Availability got 5 Yes answers?

Re: Filtering using expression

jagan — Mon, 13 Oct 2014 10:05:58 GMT

Hi,

Try like this

=Count({<Question={'*Availability*'}, Answer={'Yes'}>} Answer)

Regards,

Jagan.

Re: Filtering using expression

chiso_chiso — Mon, 13 Oct 2014 10:13:10 GMT

For some reason when i change the filter word i.e. from availability to say visibility, the result is not changing which is not a true reflection. any ideas why it may be so?

Re: Re: Filtering using expression

ashfaq_haseeb — Mon, 13 Oct 2014 10:19:24 GMT

Check this

Regards

ASHFAQ

Re: Re: Filtering using expression

chiso_chiso — Mon, 13 Oct 2014 10:25:18 GMT

Thanks guys. Further, help if I want to get average of each answer

Re: Re: Re: Filtering using expression

ashfaq_haseeb — Mon, 13 Oct 2014 10:30:13 GMT

Hi,

Check this

Regards

ASHFAQ

Re: Re: Re: Filtering using expression

chiso_chiso — Mon, 13 Oct 2014 10:38:33 GMT

I am successful, thanks to you colleagues.

Re: Re: Re: Filtering using expression

ashfaq_haseeb — Mon, 13 Oct 2014 10:42:42 GMT

Welcome

Re: Re: Re: Re: Filtering using expression

chiso_chiso — Tue, 14 Oct 2014 07:17:34 GMT

One more thing, how do I filter 2 words from the same field e.g. Availability, 20l and coke using the attached example.

Re: Filtering using expression

ashfaq_haseeb — Tue, 14 Oct 2014 07:22:16 GMT

Hi,

I have seen attachment, its not clear.

Can you explain a bit more?

Regards

ASHFAQ

Re: Filtering using expression

chiso_chiso — Tue, 14 Oct 2014 07:28:44 GMT

Thanks Ashfaq,

I want the total for Availability, then drilled down to Total Availability of Coke (as brand) and Total Availability of 20l(as SKU). If possible in one chart.

Regards

Re: Re: Filtering using expression

ashfaq_haseeb — Tue, 14 Oct 2014 07:42:17 GMT

Hi,

May be like this.

Check Sheet1 in attached application

If it answered then mark it as helpful.

Regards

ASHFAQ

Re: Re: Filtering using expression

chiso_chiso — Tue, 14 Oct 2014 07:48:03 GMT

This is very helpful. Please explain it.

Re: Filtering using expression

ashfaq_haseeb — Tue, 14 Oct 2014 07:51:54 GMT

Hi,

If you see attached excel I have added new field called Map to map all Availability, Visibility and Spacing in 1 group.

In fronted I have created a drill down group which fill first show total availability when you click on it it will drill to all available SKU's

Hope it helps

Regards

ASHFAQ

Re: Filtering using expression

chiso_chiso — Tue, 14 Oct 2014 07:54:59 GMT

I see.. But isnt there a way of filtering using an expression like 'if question has a word availability and coke, then count'

Re: Filtering using expression

ashfaq_haseeb — Tue, 14 Oct 2014 08:00:27 GMT

We can but that will not allow you to drill.

Regards

ASHFAQ

Re: Filtering using expression

chiso_chiso — Tue, 14 Oct 2014 08:01:57 GMT

Please explain, and leave out the drilling.