Showing N/A for max year null value

[deepakqlikview_123]

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

[mohamed_ahid]

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

[sunny_talwar · Accepted solution]

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)

[deepakqlikview_123]

Thanks Sunny your solution rocked

[deepakqlikview_123]

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

[sunny_talwar]

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)