Value for max(date) in a month

[#1]

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.

[MK_QSL]

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)

or

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

[#3]

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

[MK_QSL]

FirstSortedValue(Amount,-Date)

EDIT:

or use this

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

[Anonymous]

Hi,

Try it

[#6]

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