topic Re: How to use max(Date) on a date range in a Bar Chart in QlikView

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

Mon, 10 Nov 2014 06:34:54 GMT

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.

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

SergeyMak — Mon, 10 Nov 2014 06:37:55 GMT

Hi,

Share your app, pls, or sample dataset.

Regards,

Sergey

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

Mon, 10 Nov 2014 07:05:18 GMT

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

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

Mon, 10 Nov 2014 07:05:20 GMT

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>

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

SergeyMak — Mon, 10 Nov 2014 07:23:32 GMT

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.

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

SergeyMak — Mon, 10 Nov 2014 07:25:31 GMT

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

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

Mon, 10 Nov 2014 08:14:16 GMT

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

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

Mon, 10 Nov 2014 08:14:20 GMT

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 <

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

SergeyMak — Mon, 10 Nov 2014 08:35:02 GMT

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

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

Mon, 10 Nov 2014 11:58:10 GMT

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

Appreciate your help.

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

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

Tue, 11 Nov 2014 13:58:20 GMT

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>

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

Tue, 11 Nov 2014 14:00:33 GMT

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

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

SergeyMak — Wed, 12 Nov 2014 01:59:12 GMT

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

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

Wed, 12 Nov 2014 05:35:34 GMT

Can you please give me an example with that RevPar expression

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

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

SergeyMak — Wed, 12 Nov 2014 05:43:59 GMT

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?

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

Wed, 12 Nov 2014 05:55:23 GMT

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 <

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

SergeyMak — Wed, 12 Nov 2014 07:24:11 GMT

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