Sorting on date attribute

[iolaper]

I came across an interesting situation. I have the following data:

Date Type
3/9/2004 12:00:00 am Acquisition
3/10/2004 12:00:00 am Acquisition
3/12/2004 12:00:00 am Acquisition
3/8/2004 12:00:00 am Expansion
3/9/2004 12:00:00 am Expansion
3/10/2004 12:00:00 am Expansion


I am trying to get the report to display the attribute with the oldest date i.e :

Date Type
3/9/2004 12:00:00 am Acquisition
3/8/2004 12:00:00 am Expansion

I am sure there is a simple way to do this. Maybe I just forgot.

Thanks a million guys!

 

[kpescatore]

Is there a reason you can't sort on the date in desc order by right clicking on the date column or through the Data -->advanced sorting menu?

Is the Date stored as a text?

 

[iolaper]

The date is created from the dateid fact and is in 'datetime' format. I want to be a little more elaborate and give an example.

Date Type
3/9/2004 12:00:00 am Acquisition
3/10/2004 12:00:00 am Acquisition
3/12/2004 12:00:00 am Acquisition
3/8/2004 12:00:00 am Consulting Fee
3/9/2004 12:00:00 am Consulting Fee
3/10/2004 12:00:00 am Consulting Fee
3/11/2004 12:00:00 am Consulting Fee
3/10/2004 12:00:00 am Disposition
3/11/2004 12:00:00 am Disposition
3/12/2004 12:00:00 am Disposition
3/8/2004 12:00:00 am Expansion
3/9/2004 12:00:00 am Expansion
3/10/2004 12:00:00 am Expansion
3/11/2004 12:00:00 am Expansion
3/12/2004 12:00:00 am Expansion
3/8/2004 12:00:00 am New Lease
3/9/2004 12:00:00 am New Lease
3/10/2004 12:00:00 am New Lease
3/11/2004 12:00:00 am New Lease
3/12/2004 12:00:00 am New Lease
3/10/2004 12:00:00 am Referral Fee
3/11/2004 12:00:00 am Referral Fee
3/12/2004 12:00:00 am Referral Fee

Notice that for each type there are dates. Of these, for each type I want the oldest date to display, as follows

Date Type
3/9/2004 12:00:00 am Acquisition
3/8/2004 12:00:00 am Consulting Fee
3/10/2004 12:00:00 am Disposition
3/8/2004 12:00:00 am Expansion
3/8/2004 12:00:00 am New Lease
3/10/2004 12:00:00 am Referral Fee


Hope this helps. I can provide any other information if needed too.

Thanks again

 

[kpescatore]

ok I felt bad about my first post so I worked on this a little bit and I found a solution but its not really a good one.
You will need to create a couple of new metrics just for this result.
The first metric is just a unique count for the each row returned so the count = 1 for each row.
The second metric is a running count with a break on Field2 sorted by field1
The last you can create as a new metric in the result and its just a rank or the running count with asc=False.

Then sort by the rank column. This I think will get you the order that you are looking for.

Examples

Field1 = Date
Field2 = type

Metric Defs
Count1 = Count([Field2]) {[Field1], [Field2]}
Running Count = RunningCount<BreakBy={[Field2]}, SortBy= ([Field1]) >([count1])

Report Level Metric
Rank Running Count = Rank<ASC=False, BreakBy={[Field2]}>([running count])

I get the following as output. I think This is what you are looking for.

		        Metrics	count1	running count      Rank (running count)Break by (Field2)
Field1	Field2				
03/11/2004	Consulting Fee    1	4	1
03/11/2004	Referral Fee      1	2	1
03/12/2004	Acquisition       1	3	1
03/12/2004	Disposition       1	3	1
03/12/2004	Expansion         1	5	1
03/12/2004	New Lease         1	5	1
03/10/2004	Acquisition       1	2	2
03/10/2004	Consulting Fee    1	3	2
03/10/2004	Referral Fee      1	1	2
03/11/2004	Disposition       1	2	2
03/11/2004	Expansion         1	4	2
03/11/2004	New Lease         1	4	2
03/09/2004	Acquisition       1	1	3
03/09/2004	Consulting Fee    1	2	3
03/10/2004	Disposition       1	1	3
03/10/2004	Expansion         1	3	3
03/10/2004	New Lease         1	3	3
03/08/2004	Consulting Fee    1	1	4
03/09/2004	Expansion         1	2	4
03/09/2004	New Lease         1	2	4
03/08/2004	Expansion         1	1	5
03/08/2004	New Lease         1	1	5

 

[iolaper]

Hey kpescatore
That was some great work done. That was almost the similar concept I was looking for.
But I am looking for only the 6 types to display in my final report with their oldest dates AND NOTHING ELSE.

Here is what i did.
I created a metric using the dateid fact as follows
Min([Invoice Date]) {~}
Level: Report level
Filtering: Absolute
Grouping: Standard

I placed the date and type attributes on the report and performed an advanced sort on the type then the date. I was able to view the minimum dates.

Correct me if I am wrong.

A big thanks once again!