How to use max(Date) on a date range in a Bar Chart

[#1]

I need to show values considering only the maximum(As of date) in a Bar chart. When I select a year from the filter the bar chart should show 4 years before and 4 years after the selected year.I am using the below expression in the Bar chart:

=num(Round((avg({$<As_Of_Date={'$(vMax_As_Of_Date_GDP)'},Year_No = {">=$(vPrev4CurrentYear) <=$(vFuture4CurrentYear) "},Flag= {'GDP'},Country={'US'}>} GDP_Forecast)),0.1)/100,'#,##0.0%')

vMax_As_Of_Date_GDP=max({<Flag={'GDP'}>}As_Of_Date)

If the "As of Date" is same for all the years then there is no issue.But if it is different for some year then chart does not show the value for that year.I have attached the screenshots where in first case when "As of Date" is same I see data for the entire Year range but when "As of Date" is different I don't see the year for which "As of Date" is different.

Please help.

[SergeyMak]

Hi,

Share your app, pls, or sample dataset.

Regards,

Sergey

[#3]

Hi Sergey,

Thank you for the reply.PFA the application along with source excel."As of Date" for "2014" is different from "2013","2015" and "2016" then even if I select year "2015" in the filter, chart will show value for 2013,2015 and 2016 but not 2014.If I select year "2014" then I will get only one value for '"2014" and not the entire year range.I have attached the screenshots where in first case when "As of Date" is same I see data for the entire Year range but when "As of Date" is different I don't see the year for which "As of Date" is different.

Regards,

Harshad

[#4]

Hi Sergey,

Thank you for the reply.PFA the application along with source excel."As of

Date" for "2014" is different from "2013","2015" and "2016" then even if I

select year "2015" in the filter, chart will show value for 2013,2015 and

2016 but not 2014.If I select year "2014" then I will get only one value

for '"2014" and not the entire year range.I have attached the screenshots

where in first case when "As of Date" is same I see data for the entire

Year range but when "As of Date" is different I don't see the year for

which "As of Date" is different.

Regards,

Harshad

--

This communication is confidential and subject to and governed by the

confidentiality and use restrictions contained in Saama’s Electronic

Communications Disclaimer. <http://www.saama.com/disclaimer>

[SergeyMak]

I see, that when you select 2014 your expression is

=num(Round((avg({$<As_Of_Date={'8/23/2014'},Year_No = {">=2010 <=2018 "},Country={'US'}>} GDP_Forecast)),0.1)/100,'#,##0.0%')

and you have actual data for US and 2014 and 8/23/2014.

But if you select 2015 for example your expression is

=num(Round((avg({$<As_Of_Date={'4/1/2014'},Year_No = {">=2011 <=2019 "},Country={'US'}>} GDP_Forecast)),0.1)/100,'#,##0.0%')

and you don't have 2014 data for 4/1/2014 date and Country US.

[SergeyMak]

So, my question is What do you actually need to see? The usage of "As of Date"  is not clear for me.

[#7]

I need to show GDP values for each year only for the latest "As of Date".If max(As of Date) is 4/1/2014 for 2013, 8/23/2014 for 2014 and 10/1/2014  for 2015 then my chart should show the corresponding value for 4/1/2014 for 2013, 8/23/2014 for 2014 and 10/1/2014 for 2015 when I select 2013 from the filter.Since multiple forecasts(distinguised by As of Date) for GDP are done, user wants to see only the value corresponding from the latest "As of Date".

eg: if there are 2 GDP values for year 2014

1) 8.4 with As of Date-04/01/2014

2) 5.6 with As of Date-08/23/2014.

Then chart needs to show GDP value as 5.6 for year 2014

[#8]

I need to show GDP values for each year only for the latest "As of Date".If

max(As of Date) is 4/1/2014 for 2013, 8/23/2014 for 2014 and 10/1/2014 for

2015 then my chart should show the corresponding value for 4/1/2014 for

2013, 8/23/2014 for 2014 and 10/1/2014 for 2015 when I select 2013 from the

filter.Since multiple forecasts(distinguised by As of Date) for GDP are

done, user wants to see only the value corresponding from the latest "As of

Date".

eg: if there are 2 GDP values for year 2014

1) 8.4 with As of Date-04/01/2014

2) 5.6 with As of Date-08/23/2014.

Then chart needs to show GDP value as 5.6 for year 2014

On Mon, Nov 10, 2014 at 12:56 PM, Sergey Makushinsky <

[SergeyMak · Accepted solution]

Try to use the following expression

num(Round(
FirstSortedValue({<Country={'US'},Year_No={">=$(vPrev4CurrentYear) <=$(vFuture4CurrentYear) "}>}GDP_Forecast,-As_Of_Date)
,0.1)/100,'#,##0.0%')

PFA an example

[#10]

Thank you so much Sergey.It made my life easy.

Appreciate your help.

On Mon, Nov 10, 2014 at 2:05 PM, Sergey Makushinsky <