Need max year, max month and max day using set analysis

[#1]

Dear All,

i need to show max year, max month & max day of sales, so i used following function , but its not showing the amount of sales.

Pls see below function.

=Sum({$<Year = {$(=max(Year))}, Month = {$(=month(addmonths(max(Month) , -1)))},Day = {$(=max(Day))}>} Amount)

Regards,

Antony.

[#2]

Hi Deepak,

I checked both the condition which you mentioned on above , its fine.

When i use the below condition then its showing the correct Amount for max year, month & date.

=sum(if(Date=DateMax,Amount))

Also one more query.

The following expressions are same or different, Because i'm getting different amount for below expressions.

1) =sum({$<Year={$(=max(Year))}>} Amount)

2) = sum(if(Year=yMax,Amount))

[deepakk]

Hi,

Logically both should display same value if yMax = Max(Year),

If in variable yMax you have to specify "=". If you miss it , it might cause a problem.

[#4]

I have not missed , i used "=" sign for below variable.

yMax = max(Year)

[deepakk]

hi,

I made a sample test file and tested both the expression and its showing me same value.

Can you attach you application so that we can test it out.

[#6]

Hi Deepak,

PFA.

Regards,

Antony

[deepakk]

Its little weird. Its working fine in my case.

Anyways I always prefer set analysis over If statement and the Set analysis part in your application working fine.

Best of Luck...

[#8]

Hi,

Have you found Answer for max year, max month & max day using set analysis.

[deepakk]

I got it .... I got it...... I got it....

In you case the value is coming zero because the Max(Day) is coming as 31. so the maximum date becomes 31-dec-2010 and you have data only till 3rd Dec.

so use below expression

sum({$<Year = {$(=max(Year))},MonthNum = {$(=max(MonthNum))},Day = {$(= Day(max(Date(Date,'DD/MM/YYYY'))))}>}Amount)

For the if problem you have to use the if statement in below manner.

if(max(Year)=yMax,Sum(Amount))

I hope this will close the issue

[#10]

Hi Deepak,

Can i have your mail id pls.

[deepakk]

kurup.d@gmail.com