topic Re: how to use conditional dimension to filter data in QlikView

how to use conditional dimension to filter data

pashok75 — Tue, 21 Nov 2017 08:42:56 GMT

Dear all,

I have 4 cities A,B,C,D in the dimension.

CITY	PRODUCTION
A	1000
B	2000
C	1000
D	2000

I need to filter on A,B through calculated dimension. How can use the following functions.

1. =

2. <>

3. Like

4. Not Like

Ashok

Re: how to use conditional dimension to filter data

Anil_Babu_Samineni — Tue, 21 Nov 2017 09:05:00 GMT

What are you doing with filter CITY?

Re: how to use conditional dimension to filter data

pashok75 — Tue, 21 Nov 2017 09:06:31 GMT

In the chart i need to show only data for city A, B. I need to hide C,D

Re: how to use conditional dimension to filter data

pashok75 — Tue, 21 Nov 2017 12:37:48 GMT

Can anyone help me out in this

Re: how to use conditional dimension to filter data

Or — Tue, 21 Nov 2017 12:40:15 GMT

If(City='A' or City='B',City) and Suppress Null is the trivial solution. If you elaborate on what you need, we might be able to suggest a more robust approach.

Re: how to use conditional dimension to filter data

Anil_Babu_Samineni — Tue, 21 Nov 2017 12:44:13 GMT

Then ,Why can't this?

Sum({<CITY = {'A','B'}>} PRODUCTION)

Re: how to use conditional dimension to filter data

stabben23 — Tue, 21 Nov 2017 12:48:46 GMT

Set analysis as Anil sugest is the best way to filter Dimensions.

Sum({<CITY = {'A','B'}>} PRODUCTION) gives you A and B

Sum({<CITY -= {'A','B'}>} PRODUCTION) not A and B

Sum({<CITY = {'A*'}>} PRODUCTION) Like, only A, and wildcard if city like Amsterdam

Re: how to use conditional dimension to filter data

pashok75 — Tue, 21 Nov 2017 12:49:37 GMT

I tried but it is still showing all values.

IF(T_SC_NAME = 'COMP' OR T_SC_NAME = 'TP', T_SC_NAME)

in the above statement T_SC_NAME is my Dimension. I need to show values COMP, TP and eliminate TENANTS, WWP. Check the screenshot for better understanding

Re: how to use conditional dimension to filter data

Or — Tue, 21 Nov 2017 13:01:33 GMT

You can't use Conditional Display to filter data. It's as simple as that. You have to filter through a calculated dimension or through the expression (the latter is recommended).

Re: how to use conditional dimension to filter data

pashok75 — Tue, 21 Nov 2017 13:04:38 GMT

Dear Staffan,

The statement is working. But the problem is I am using a variable which has a complex formula to calculate the value and because of that system giving an error allocated memory exceeded. Is there a way to have a condition only based on dimension and inputing the expression in the statement.

For e.g this is my statement in the ENABLE CONTIONAL

SUM({<T_SC_NAME = {'COMP','TP'}>}$(vCountYTD))

Formula in VARIABLE vCountYTD

(Count(DISTINCT{<T_PREM_TYPE={'N','R'}>}if(T_APPR_DT <= AddYears(max(TOTAL T_APPR_DT), T_YEAR - year(max(TOTAL T_APPR_DT))), T_POL_NO))-Count({<T_PREM_TYPE={'C'}>}if(T_APPR_DT <= AddYears(max(TOTAL T_APPR_DT), T_YEAR - year(max(TOTAL T_APPR_DT))), T_POL_NO)))

Re: how to use conditional dimension to filter data

pashok75 — Tue, 21 Nov 2017 13:05:11 GMT

NOT inputing the expression in the statement.

Re: how to use conditional dimension to filter data

pashok75 — Tue, 21 Nov 2017 13:11:30 GMT

Dear Shoham,

Once I made it calculated dimension the data filters. Thank you so much.

Re: how to use conditional dimension to filter data

Or — Tue, 21 Nov 2017 13:15:22 GMT

Glad to help.

What I would recommend in this case is to create a filtered dimension in your script (with the same syntax) and then use it as a chart dimension. It's "healthier" than using a calculated dimension in charts and should also be quicker / less resource intensive.