Displaying the value of the minimum value in date field

[#1]

I have data looks like the following table. the date field is linked to a master Calendar table where I could slice the data by day, week, month Quarter and year.

I am trying to display the total monthly New, closed issues, and on the same chart the open issues where the open monthly issues represented only by the first day of the month number of open items only (i.e : for Sep open issues is 10 for the 2009-09-01).

Sep New issues = Sum(new)

Sep Closed Issues=Sum (Closed)

Sep Open Issues = open where date is the minimum date of the current selected data range (I am having difficulties with this part)

DATE	New	Closed	Open
9/1/2009	5	2	10
9/2/2009	10	5	2
9/3/2009	5	2	2
9/4/2009	10	5	2
9/5/2009	5	2	2
9/6/2009	10	5	2
9/7/2009	5	2	2
9/8/2009	10	5	2
9/9/2009	5	2	2
9/10/2009	10	5	2
9/11/2009	5	2	2
9/12/2009	10	5	2
9/13/2009	5	2	2
9/14/2009	10	5	2
9/15/2009	5	2	2
9/16/2009	10	5	2
9/17/2009	5	2	2
9/18/2009	10	5	2
9/19/2009	5	2	2
9/20/2009	10	5	2
9/21/2009	5	2	2
9/22/2009	10	5	2
9/23/2009	5	2	2
9/24/2009	10	5	2
9/25/2009	5	2	2
9/26/2009	10	5	2
9/27/2009	5	2	2
9/28/2009	10	5	2
9/29/2009	5	2	2
9/30/2009	10	5	2
10/1/2009	5	2	10
10/2/2009	10	5	2
10/3/2009	5	2	2
10/4/2009	10	5	2
10/5/2009	5	2	2
10/6/2009	10	5	2
10/7/2009	5	2	2
10/8/2009	10	5	2
10/9/2009	5	2	2
10/10/2009	10	5	2
10/11/2009	5	2	2
10/12/2009	10	5	2
10/13/2009	5	2	2
10/14/2009	10	5	2
10/15/2009	5	2	2
10/16/2009	10	5	2
10/17/2009	5	2	2
10/18/2009	10	5	2
10/19/2009	5	2	2
10/20/2009	10	5	2
10/21/2009	5	2	2
10/22/2009	10	5	2
10/23/2009	5	2	2
10/24/2009	10	5	2
10/25/2009	5	2	2
10/26/2009	10	5	2
10/27/2009	5	2	2
10/28/2009	10	5	2
10/29/2009	5	2	2
10/30/2009	10	5	2
10/31/2009	5	2	2
11/1/2009	10	5	10
11/2/2009	5	2	2
11/3/2009	10	5	2
11/4/2009	5	2	2
11/5/2009	10	5	2
11/6/2009	5	2	2
11/7/2009	10	5	2
11/8/2009	5	2	2
11/9/2009	10	5	2
11/10/2009	5	2	2
11/11/2009	10	5	2
11/12/2009	5	2	2
11/13/2009	10	5	2
11/14/2009	5	2	2
11/15/2009	10	5	2
11/16/2009	5	2	2
11/17/2009	10	5	2
11/18/2009	5	2	2
11/19/2009	10	5	2
11/20/2009	5	2	2
11/21/2009	10	5	2
11/22/2009	5	2	2
11/23/2009	10	5	2
11/24/2009	5	2	2
11/25/2009	10	5	2
11/26/2009	5	2	2
11/27/2009	10	5	2
11/28/2009	5	2	2
11/29/2009	10	5	2
11/30/2009	5	2	2

[#2]

I'm not sure I totally understand what you're after, but according to your description of Sep Open Issues, I would try:

Sum({<Date = {"$(=Min(Date))"}>} Open)

That will give you the Sum of the Open field when the Date is equal to the smallest selected date.

[#3]

Thanks for your response. I can not use the proposed solution because I am still using version 8.2 which does not support Set analysis.

is there another way to do that without using set analysis.

I have tried to use "First" function but it is not working for me

sum(NewIssues)+First(OpenIssues)

[#4]

Try:

Sum(if(date1 = '$(=Min(date1))', Open))

[#5]

This one looked very promising but the problem becuase I am not making a selection on a specifc day the Date (DTE) will never be evaluated and resulted zeros all the time.

I looking for the month data (the dim is month and I have the data on the day level DTE)

Sum( if(DTE = '$(=Min(DTE))', OpenIssues)) still giving zero bcz DTE=DTE = '$(=Min(DTE)) never gets evaluted bcz not specifc Date. they are 30 different dates to pick the first openissues value where date=min(date) or first date of th etime period.

[rwunderlich]

It's a bit ugly, but I think the expression would be something like:

=Sum(if(num(Date) = aggr(min(Date), Month), Open, 0))

-Rob

[#7]

Thanks Rob. this works great.

Can I take this one step further and replace the month with a Group (Day, Week, Month, Quater,Year)? the logic works the same for all of them but I am not sure how to replace the Dim Month with the Group Name!!

please help

[rwunderlich]

I believe if you just substitute "Month" with the Group name, it will work. For example, if your groupname is "TimeDrill" then:

=Sum(if(num(Date) = aggr(min(Date), TimeDrill), Open, 0))

-Rob

[#9]

thanks alot guys. it is working great now.

[#10]

I got this Sum(if(num(Date) = aggr(min(Date), Month), Open, 0)) to work great for pulling the min date value replacing Month with Quarter.  When trying to get the max date by changing min to max it didn't work?  Thoughts?