12 Replies Latest reply: Nov 21, 2017 8:15 AM by Or Shoham

# how to use conditional dimension to filter data

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

What are you doing with filter CITY?

• ###### Re: how to use conditional dimension to filter data

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

Then ,Why can't this?

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

• ###### Re: how to use conditional dimension to filter data

Can anyone help me out in this

• ###### Re: how to use conditional dimension to filter data

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

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

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

Dear Shoham,

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

• ###### Re: how to use conditional dimension to filter data

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.

• ###### Re: how to use conditional dimension to filter data

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

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

NOT inputing the expression in the statement.