Max and Min calculation side by side

Hi Guys ,

I dont know why i was thinking we cannot calculate Max and Min side by side.It took me a while to figure out.Below is the case

Use go sales ,sales namespace .Take Current year, date,Quantity ..filter current year to be 2004 .

1) try to take max(date for currentyear) and Min(date) for currentyear you will see that you are getting Jan 1 2004 and Jan 31 2004 .Which is as what you expect.

Now put quantity next to it.You will see that you get 0 for max quantity.The reason is there is no value for Jan 1 2004 in database similary for Jan 31 2004 . So if you want the next date for which actual values are there put filter quantity is not null .

Still one more thing remains .You want to show only max and Min date quantities

use total( case when ( max_date =date) then (quantity) else (0) end).Use aggregate as calculated and roll up as total.Note this is very essential
as without putting total outside you will end up getting all weird answers

.