topic Showing N/A for max year null value in QlikView

Showing N/A for max year null value

deepakqlikview_123 — Wed, 02 Dec 2015 07:50:49 GMT

Hi All,

PFA,

I have different values for different indicators for different years.

As per requirement I need to show N/A only when values are not there for max year for each expression for any country selection.

Can you please suggest how shall i go ahead.

Thanks

Re: Showing N/A for max year null value

mohamed_ahid — Wed, 02 Dec 2015 10:32:27 GMT

Hi can you be more explicit and may be give us an exemple plz ?

Re: Showing N/A for max year null value

sunny_talwar — Wed, 02 Dec 2015 11:37:05 GMT

This Deepak?

Expression:

If(YEAR = Max(TOTAL YEAR) and Len(Trim(INDICATOR_VALUE)) = 0, 'N/A',

if(WildMatch(Country,'United States'),if(Avg({<INDICATOR_NAME={'Nominal GDP Growth minus 10 YR Bond Yield_US'},FRQ_TYPE={'A'}>}INDICATOR_VALUE)<=0,

Avg({<INDICATOR_NAME={'Nominal GDP Growth minus 10 YR Bond Yield_US'},FRQ_TYPE={'A'}>}INDICATOR_VALUE)),

if(Avg({<INDICATOR_NAME={'Nominal GDP Growth minus 10 YR Bond Yield'}>}INDICATOR_VALUE)<=0,

Avg({<INDICATOR_NAME={'Nominal GDP Growth minus 10 YR Bond Yield'}>}INDICATOR_VALUE)))/100)

Re: Showing N/A for max year null value

deepakqlikview_123 — Wed, 02 Dec 2015 14:40:32 GMT

Thanks Sunny your solution rocked

Re: Showing N/A for max year null value

deepakqlikview_123 — Fri, 04 Dec 2015 07:34:32 GMT

Hi Sunny,

Instead of showing N/A i need to hide whole row for max year if data is not there.

is.Mean if data is not there for 2015 , 2015 row should not be displayed

Can you please suggest on this

thanks

Re: Showing N/A for max year null value

sunny_talwar — Fri, 04 Dec 2015 11:48:58 GMT

Use this as your calculated dimension:

=Aggr(If(YEAR = Max(TOTAL YEAR) and Len(Trim(INDICATOR_VALUE)) = 0, Null(),

if(Country='United Kingdom',if(YEAR>=1960,YEAR),

if(WildMatch(Country,'Japan'),if(YEAR>=1995,YEAR)

,if(WildMatch(Country,'Spain'),if(YEAR>=1981,YEAR),if(WildMatch(Country,'Mexico'),if(YEAR>=2001,YEAR),if(WildMatch(Country,'Canada'),if(YEAR>=1989,YEAR)

,if(WildMatch(Country,'Australia'),if(YEAR>=1986,YEAR),if(WildMatch(Country,'China'),if(YEAR>=2004,YEAR),if(WildMatch(Country,'France'),if(YEAR>=1984,YEAR)

,if(WildMatch(Country,'Germany'),if(YEAR>=1993,YEAR),if(WildMatch(Country,'Italy'),if(YEAR>=1990,YEAR),if(WildMatch(Country,'Ireland'),if(YEAR>=1998,YEAR),

if(WildMatch(Country,'United States'),if(YEAR>=1962,YEAR),if(WildMatch(Country,'Brazil'),if(YEAR>=2007,YEAR),if(WildMatch(Country,'India'),if(YEAR>=1999,YEAR)

,YEAR) )))))))))))))), YEAR)