topic Re: Use a table to filter but keep all lines in the table in QlikView

Use a table to filter but keep all lines in the table

xxxx2 — Mon, 03 Mar 2025 15:19:54 GMT

Hello,

I have a table with two dimensions and a calculated expression, and I would like to use this table to filter data on other graphs.

I would like this table to keep all lines when filtering. Is it possible ?

This is what my table look like :

But when I select something :

I would like other graphs to be filtered but this table to stay the same as when no "Client" and no "Recap" are selected (just those 2, I have other selected fields that I want to keep).

Dimensions :

- Client

- =if(Recap = 'LAY' or Recap = 'ENF'
, Recap
)

Expression :

= Aggr(

sum( IF(

IF(((Sais / Sais_Soc) -1) < £Ecart_Sais, 1, 0)

IF(((An / An_Soc) -1) > £Ecart_An, 1, 0)

IF(((QtFin / Cons) -1) > £Ecart_Cons, 1, 0)

= 3

, 1

, 0

)

, Client, Recap

)

Thanks for your help

Re: Use a table to filter but keep all lines in the table

diegozecchini — Mon, 03 Mar 2025 16:29:06 GMT

Hi!
try to change this:

= Aggr(
sum( {< Client=, Recap= >} IF(

IF(((Sais / Sais_Soc) -1) < £Ecart_Sais, 1, 0)
+
IF(((An / An_Soc) -1) > £Ecart_An, 1, 0)
+
IF(((QtFin / Cons) -1) > £Ecart_Cons, 1, 0)
= 3
, 1
, 0
)
),
Client, Recap
)

{< Client=, Recap= >} tells Qlik to ignore selections on Client and Recap, keeping all rows in the table.

Re: Use a table to filter but keep all lines in the table

xxxx2 — Tue, 04 Mar 2025 07:54:23 GMT

Hello, thank you for your answer.

I changed the expression but lines still disappear in the table when selecting :

Expression :

= Aggr(

sum( {< CdClient = , Recap = >}

IF(

IF(((Sais / Sais_Soc) -1) < £Ecart_Sais, 1, 0)

IF(((An / An_Soc) -1) > £Ecart_An, 1, 0)

IF(((QtFin / Cons) -1) > £Ecart_Cons, 1, 0)

= 3

, 1

, 0

)

, CdClient, Recap

)

I would like it to stay like the first picture...

Re: Use a table to filter but keep all lines in the table

marcus_sommer — Tue, 04 Mar 2025 12:12:58 GMT

You may need a manipulating of the selection state within each aggregation part, like:

sum({ Set } aggr({ Set } sum({ Set } Expr), Dim1, Dim2))

Re: Use a table to filter but keep all lines in the table

xxxx2 — Tue, 04 Mar 2025 12:31:38 GMT

@marcus_sommer

It seems to work perfectly thank you !

Do you have an explanation of why I need to sum the whole previsous expression ?

Expression :

= Sum ({< CdClient = , Recap = >}

Aggr( {< CdClient = , Recap = >}

sum( {< CdClient = , Recap = >}

IF(

IF(((Sais / Sais_Soc) -1) < £Ecart_Sais, 1, 0)

IF(((An / An_Soc) -1) > £Ecart_An, 1, 0)

IF(((QtFin / Cons) -1) > £Ecart_Cons, 1, 0)

= 3

, 1

, 0

)

, CdClient, Recap

)

Re: Use a table to filter but keep all lines in the table

Padma123 — Tue, 04 Mar 2025 12:46:43 GMT

use this expression

sum({1}Aggr(

sum( IF(

IF(((Sais / Sais_Soc) -1) < £Ecart_Sais, 1, 0)

IF(((An / An_Soc) -1) > £Ecart_An, 1, 0)

IF(((QtFin / Cons) -1) > £Ecart_Cons, 1, 0)

= 3

, 1

, 0

)

, Client, Recap

)

Re: Use a table to filter but keep all lines in the table

marcus_sommer — Tue, 04 Mar 2025 12:52:46 GMT

Applying something like: aggr(sum(Expr), Dim) is not an expression else it creates a calculated dimension. Only if the aggr() is wrapped with any aggregation-function like sum/count/concat() it becomes an expression again.

In many cases the aggr() output could be adjusted by using: aggr(NODISTINCT sum(Expr), Dim) but logically it remains a calculated dimension and depending on the data-model/data-set and the object-dimensionalities the results may not always be like expected.

Re: Use a table to filter but keep all lines in the table

xxxx2 — Tue, 04 Mar 2025 12:53:44 GMT

Thank you for your answer.

It keeps all the lines but the expression is not evaluated for the non selected ones.

Re: Use a table to filter but keep all lines in the table

xxxx2 — Tue, 04 Mar 2025 13:00:28 GMT

Ok thank you

And I need to put the {set} within each aggregation part otherwise each aggregation will be evaluated separately ? Not with the {set} of the upper one ?

Re: Use a table to filter but keep all lines in the table

marcus_sommer — Tue, 04 Mar 2025 13:25:58 GMT

No, each aggregation-part will respect the defined respectively implicit selection state. Means without specifying anything it acts against the default state and therefore it's often necessary to define the wanted selection state everywhere - and it must not be always the same else any existing alternate state might be referred and/or in any way be adjusted.

Re: Use a table to filter but keep all lines in the table

xxxx2 — Tue, 04 Mar 2025 13:30:45 GMT

Ok thank you very much for your help !

Have a good day !