How to avoid '0' expression

[NavinReddy]

Dear Experts,

please find the below screen shot,not able to avoid the '0' from chart.

i just want to display only value=1,need to avoid zeros from the chart.

please suggest.Here is the expression im using

=count(aggr(if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}Metric)<>1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)), Metric))

Thanks,

Niranjan

[MK_QSL]

Is TierRed is dimension?

if yes, use below as calculated dimension

if(TierRed = 1, TierRed)

and tick suppress when value is null

[swuehl]

If you just want to avoid the zero value, and show NULL, you would need an outer aggregation function that returns NULL if there are no values returned within the aggregation scope.

For example, replace Count() with Max() [Don't know if this is appropriate though]:

=Max(aggr(if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}Metric)<>1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)), Metric))

If you want to remove the complete table row where the line shows zero in the column, you would need to have all your expressions return zero for this line and enable 'suppress zero values' on presentation tab.

Can't see if this is the case for your chart, given the sparse information provided.

[NavinReddy]

Hi Stefan,

Thank you so much for your reply

i have tried with your expression still i have blanks

just want to display one value '1' ids,

i have attached the sample please check and any suggessions

Thanks,

Niru

[NavinReddy]

Hi Manish,

its not a dimension. it label name

its not working for me any other way

Thanks,

Niru

[swuehl · Accepted solution]

As I said, one option would be to have all expressions return zero or NULL if the first expression is not returning a value:

=If(Column(1), YourExpression)

See attached.

[NavinReddy]

HI Stefan,

Thank You so much its working fine

[NavinReddy]

Hi Stefan,

i have one problem here i don't want to show TierRed column.

its there any way to hide TierRed column

please suggest me

Thanks,

Niru

[swuehl]

Go to presentation tab, select the TierRed expression and enable 'Hide Column'

[NavinReddy]

perfect, its working thank you so much your Legend