Number of Orders by first approved date , FirstSortedValue

[separerl]

Hi !

I am trying to create a straight table which holds 2 columns.
Column 1 = FirstApprovedDate , column 2 = NumberOfOrders approved that date

The data , consists of three columns , OrderNo , Version , and ApprovedDate

I have solved the first problem of finding out when the order was first approved with the function FirstSortedValue.

But now I can't solve the next problem , of having the ApprovedDate as a dimension and the numbers of orders approved that day in the next column.

I attach a sample with my problem.

Any suggestion would be very much appreciated

/Pär

[stephencredmond]

Not sure I understand because I see it as a simple straight table with the approved date as dimension and Count(Distinct OrderNo) as the expression - no?

[#3]

I'd actually make another column (in this case, another table) in your script, called FirstApprovedDate.

I called your initial table "Orders" and added another table:

OrderMaster:
LOAD
OrderNo,
date(min(ApprovedDate)) as FirstDate
RESIDENT Orders
GROUP BY OrderNo;

Then, for your final chart, use FirstDate as the dimension, and count distict OrderNo.

I know there's a way to do what you want, with the data you have, with set analysis, but the answer just escapes me right now.

Is an additional table/column an acceptable solution?

[separerl]

Stephen Redmond wrote:
Not sure I understand because I see it as a simple straight table with the approved date as dimension and Count(Distinct OrderNo) as the expression - no?

Stephen,
If I do it as you suggest , then the result would be like this:
2009-06-26 1
2009-06-25 1
2009-06-15 1
2009-06-09 2
"Blank" 3

the wanted result should be like this:
2009-06-15 1
2009-06-09 2

/Pär

[separerl]

Sally Hurley wrote:
I'd actually make another column (in this case, another table) in your script, called FirstApprovedDate.
I called your initial table "Orders" and added another table:
OrderMaster:
LOAD
 OrderNo,
 date(min(ApprovedDate)) as FirstDate
RESIDENT Orders
GROUP BY OrderNo;
Then, for your final chart, use FirstDate as the dimension, and count distict OrderNo.
I know there's a way to do what you want, with the data you have, with set analysis, but the answer just escapes me right now.
Is an additional table/column an acceptable solution?<div></div>

Sally,

Splended !! This is a solution that is acceptable.

I actually found an other solution now as well , through calculated dimension and the aggr function:
Calculated Dimension =aggr(FirstSortedValue(distinct ApprovedDate,ApprovedDate,1),OrderNo)
Expression = count(distinct OrderNo)

I attach it here.

Thanks a lot Sally for helping me with this !

/Pär