topic Re: Value for max(date) in a month in QlikView

Value for max(date) in a month

Wed, 15 Apr 2015 15:26:10 GMT

Hi Friends:

I need help in the following

I need to create a line graph for the following data set

Date Amount

02/04/2015 10

02/02/2015 20

01/02/2015 30

01/07/2015 40

12/27/2014 50

12/15/2014 20

My line graph should show

MonthYear and the amount for the maximum date in a month

so it should have values like

Feb2015 10 // value of max date in feb

Jan2015 40 // value of max date in jan

Dec2014 50 // value of max date in Dec

How can i achieve this.

Re: Value for max(date) in a month

MK_QSL — Wed, 15 Apr 2015 15:30:56 GMT

Table:

Load

Date(MonthStart(Date),'MMMYYYY') as MonthYear;

Load

Date(Date#(Date,'MM/DD/YYYY')) as Date,

Amount

Inline

[

Date, Amount

02/04/2015, 10

02/02/2015, 20

01/02/2015, 30

01/07/2015, 40

12/27/2014, 50

12/15/2014, 20

];

Create a Line Chart

Dimension

MonthYear

Expression

Max(Amount)

Max(Aggr(SUM(Amount),MonthYear))

Re: Value for max(date) in a month

Wed, 15 Apr 2015 16:01:05 GMT

Manish:

Thanks for the quick response.

But when i try this with the expression Max(Amount) i am getting the maximum amount not the amount for the max date in a month

with other expression i am getting a larger number

Re: Value for max(date) in a month

MK_QSL — Wed, 15 Apr 2015 16:05:16 GMT

FirstSortedValue(Amount,-Date)

EDIT:

or use this

=FirstSortedValue(DISTINCT Aggr(Sum(Amount),MonthYear,Date),-Date)

Re: Value for max(date) in a month

Anonymous — Wed, 15 Apr 2015 16:21:56 GMT

Hi,

Try it

Re: Value for max(date) in a month

Wed, 15 Apr 2015 16:34:47 GMT

Jannet:

Thanks,

but this will not work. You are showing the max amount in a month

by requirement is amount for the max date in a month