Maximum date of every month

[harleen_singh]

   Hello i have a data like this

Month year	Date	Quantity
Dec 2012`	01/12/2012	5
Dec 2012`	15/12/2012	10
Jan 2013	01/01/2013	15
Jan 2013	15/01/2013	20
Feb 2013	01/02/2013	25
Feb 2013	15/02/2013	30

I am using only Month year dimension in Straight chart.

I want to see the sum of only maximum date of every month for example only date 15/12/2012 in December. Result shold look like following

Month year	Sum
Dec 2012	10
Jan 2013	20
Feb 2013	30

What formula should i use??

thanks

lavi

[perumal_41 · Accepted solution]

Hi Lavi,

Sum(if(Date=Aggr(Nodistinct Max(Date),MonthYear),Quantity,0)

Use this expression

Regards

Perumal A

[somenathroy]

sum(if(Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')=Aggr(Nodistinct Max(Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')),[Month year]),Quantity))

Regards,

Som

[#4]

Hi lavi_musiclife,

                        In the script do the following:

T1:
Load * Inline [
Month_year,Date1,Quantity
Dec 2012,01/12/2012,5
Dec 2012,15/12/2012,10
Jan 2013,01/01/2013,15
Jan 2013,15/01/2013,20
Feb 2013,01/02/2013,25
Feb 2013,15/02/2013,30
];

left join(T1)
Load Distinct
Month_year,
date(max(Date1),'DD/MM/YYYY') as MaxDate
Resident T1
Group by Month_year;

Then, in the UI, for the straight table chart:

Dimension: Month_year

Expression: =sum({$<Date1=P(MaxDate)>} Quantity)

Hope this helps. Thanks.

[nagaiank]

The attached applucation willl be of help.