topic Re: How to avoid '0' expression in QlikView

How to avoid '0' expression

NavinReddy — Sat, 18 Jun 2016 09:51:38 GMT

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

Re: How to avoid '0' expression

MK_QSL — Sat, 18 Jun 2016 10:24:39 GMT

Is TierRed is dimension?

if yes, use below as calculated dimension

if(TierRed = 1, TierRed)

and tick suppress when value is null

Re: How to avoid '0' expression

swuehl — Sat, 18 Jun 2016 10:50:06 GMT

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.

Re: How to avoid '0' expression

NavinReddy — Sat, 18 Jun 2016 18:18:20 GMT

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

Re: How to avoid '0' expression

NavinReddy — Sat, 18 Jun 2016 18:20:15 GMT

Hi Manish,

its not a dimension. it label name

its not working for me any other way

Thanks,

Niru

Re: How to avoid '0' expression

swuehl — Sat, 18 Jun 2016 18:51:56 GMT

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.

Re: How to avoid '0' expression

NavinReddy — Sat, 18 Jun 2016 19:45:41 GMT

HI Stefan,

Thank You so much its working fine

Re: How to avoid '0' expression

NavinReddy — Sat, 18 Jun 2016 20:23:34 GMT

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

Re: How to avoid '0' expression

swuehl — Sat, 18 Jun 2016 20:31:03 GMT

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

Re: How to avoid '0' expression

NavinReddy — Sat, 18 Jun 2016 20:49:25 GMT

perfect, its working thank you so much your Legend

Re: How to avoid '0' expression

NavinReddy — Mon, 27 Jun 2016 11:29:07 GMT

Hi Stefan,

Good Morning,

please help me to find on logic. not able to resolve the issue here is the path of issue

How to calculate consecutive months count

Many Thanks,

Niranjan