topic Re: Set analysis : Filters on a table in QlikView

Set analysis : Filters on a table

Wed, 25 Jul 2012 11:25:56 GMT

Dear all,

Context

I uploaded data in two QV tables :

- 'Calls' with the following fields : C_call_id, C_creation_year, C_creation_month

- 'Period' with the following fileds : P_year, P_month

(I don't want to link the two tables)

I created 3 QV objects :

- a selection list (SL1) based on P_year

- a selection list (SL2) based on P_month

- a simple table (T1) with the following dimensions : C_call_id, C_creation_year, C_creation_month

SL1 and SL2 allows me to select the period(s) that I want to analyze (/get the list of CallId)

In the table T1 I added the following set analysis as expression

SA1 : Count({$<C_creation_year=P_year>}C_call_id)

SA2 : Count({$<C_creation_month=P_month>}C_call_id)

The SA1 tells me for each callid of the table T1 if the C_creation_year of the callid fits with the selected P_year : I get '1' if it fits

=> It works fine

The SA2 tells me for each callid of the table T1 if the C_creation_month of the callid fits with the selected P_month : I get '1' if it fits

=> It works fine

In order to combine the both conditions, I created the following set analysis as expression

SA3 :

Count({$<C_creation_year=P_year, C_creation_month=P_month> }C_call_id)

The SA3 tells me for each callid of the table T1 if the C_creation_year and C_creation_month of the callid fits with the selected P_year and P_month : I get '1' if it fits. Then I replaced SA1 and SA2 by SA3. I finally made condition on SA3 to display the Callid with SA3 = 1.

=> It works fine

Questions

Q1 : I don't understand why I get a little red tic under the second paranthesis of S1 and S2.
Is there something wrong in my expressions ?

Q2 : When I select P_year = 2011 and 2012, I get SA1=1 for the whole callid with C_creation_year = 2011 or 2012 but when I select no P_year, I get systematically SA1 = 0.
I was thinking that giving no selection in a selection lists means 'I want all'. Is it not the case ?
What could I do in SA1 to get all Callid when I select no P_year ?

Q3 :

How can I hide my ES4 expression column ?

Q4 : General question

Is there any other ways to filter on table (T1) with a filter based on P_year (SL1) and P_month (SL2) without links between the QV tables 'Calls' and 'Period') ?

Thank you

Regards

Pascal

Re: Set analysis : Filters on a table

Wed, 25 Jul 2012 12:36:33 GMT

Hi Pascal,

Q1 & Q2.- Try this expresion Count({$<C_creation_year= {$(P_year)}, C_creation_month={$(P_month)}> }C_call_id)

Q3 .- In the expresion tab of chart properties you can enable or disable expresion.

I hope to help you.

Re: Set analysis : Filters on a table

Wed, 25 Jul 2012 14:25:08 GMT

Thank you Gregorio,

Q1 & Q2 :

I tried with your expression.

I don't have the little red tic anymore under the parenthesis but... it doesn't work.

My table T1 became totally empty with or without selection on P_year/P_month, with or without the condition SA3=1.

Q3 :

Thank you... it works !

Regards

Pascal

Re: Set analysis : Filters on a table

jonathandienst — Wed, 25 Jul 2012 14:41:28 GMT

For Q2, I think you need

Count({<C_creation_year=P(P_year), C_creation_month=P(P_month)>} C_call_id)

Hope that helps

Jonathan

PS - does that also answer Q4

Re: Set analysis : Filters on a table

Wed, 25 Jul 2012 14:54:59 GMT

It really works Jonathan !

I didn't know the P function. What is it exactly ? It means that it must be considered as a Parameter ?

Thanks a lot.

Regards

Pascal

Re: Set analysis : Filters on a table

jonathandienst — Wed, 25 Jul 2012 16:13:05 GMT

P(Field) = Possible values

E(Field) = Excluded values

Regards

Jonathan