Aggregate Function Help!

[Anonymous]

Hi,

I am having a problem with aggregate function which I thought I knew how it works but now I am just confused.

Scenario:

I have a set of data that looks like the image below, what I am trying to do now is to have a chart that shows the sum(Amount) of the last month of each year, therefore, the desired result should should be:

2012 : 30

2013 : 50

I have tried the expression below which I thought aggregate function should return me with the latest month of each year but it didn't give me the desired result but just empty cells :

sum({<Date={"$(=aggr(max(Date),Year))"}>} Amount)

I have attached the test application that contain the data for this scenario, appreciate if anyone can help. Thank you.

Regards.

[tresesco · Accepted solution]

Use:

=FirstSortedValue( AGGR(Sum(Amount),Date), -AGGR(Date,Date))

PFA

[Anonymous]

Hi,

see below scipt

hi see the below script,

tt:

LOAD year,

     yearmonth,

     amount

FROM

xyz.xls;

Left join

load

year,

max(yearmonth) as maxdates

resident tt group by  year;

load

year,

if(yearmonth= maxdates,'y','n') as flag,

amount

resident tt;

drop Table tt;

now use    =sum({<flag={'y'}>}amount) in your chart

anant

[sudeep_d]

Hi ,

Create a variable

maxmnth=month(max(Date))

then the expression wud be

sum(if(month(Date)=maxmnth,Amount))..

See the attached file.

Cheers!!!

[Anonymous]

Thank you Tresesco, it solved my problem!