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.

[#2]

Hi Sergey,

I was able to use the expression for one measure-GDP.However I am using

many such measures such as RevPar for which expression is not working.I

have attached the excel files along with the qvw.The issue is with the

chart titled "RevPar".

Can you please help

Regards,

Harshad

On Mon, Nov 10, 2014 at 5:28 PM, Harshad Dayalu <harshad.dayalu@saama.com>

[#3]

Hi Sergey,

I was able to use the expression for one measure-GDP.However I am using many such measures such as RevPar for which expression is not working.I have attached the excel files along with the qvw.The issue is with the chart titled "RevPar".

Can you please help

Regards,

Harshad

[SergeyMak]

Hi Harshad,

You issue is there are several lines for one date, because you have data for several cities.

If you need to show the sum for all these cities you should aggregate it with aggr function

Regards,

Sergey

[#5]

Can you please give me an example with that RevPar expression

On Wed, Nov 12, 2014 at 7:30 AM, Sergey Makushinsky <

[SergeyMak]

It depends what you need to do with values of RP_Forecast of different cities. Do you need to take the average or sum of one year and as_of_date or what?

[#7]

I need to take average of all the cities of country 'US' for each year only

for the max of As of Date.

eg: For year 2013,avg(RP_Forecast) for all cities of Country 'US' should be

5.6 since the max of As of Date is 9/15/2014.

Attached the RevPar source file and qvw

On Wed, Nov 12, 2014 at 11:14 AM, Sergey Makushinsky <

[SergeyMak]

Hi,

I changed load script, added the following

LEFT JOIN (Data)
LOAD Distinct
Country,
Flag,
Year_No,
Date(Max(As_Of_Date)) AS As_Of_Date,
1 AS LastAsOfDate
Resident Data
Group by Country,
Flag,
Year_No;
 

And to calculate you can use the following expression

=Avg({$<Year_No = {">=$(vPrev4CurrentYear) <=$(vFuture4CurrentYear) "},Country={'US'},Flag={'RevPar'},LastAsOfDate={1}>}Aggr(Avg({$<Year_No = {">=$(vPrev4CurrentYear) <=$(vFuture4CurrentYear) "},Country={'US'},Flag={'RevPar'},LastAsOfDate={1}>}RP_Forecast),Country,As_Of_Date,Year_No))

PFA