Show Minimum Date

[Anonymous]

Hi All,

I have a table that shows lots and dates. i wish to present the earliest date a product entered the system.

Catalog number	Stock_Lot	Date1	Date2	Stock	Orders	Forecast
123456	G0121761	19/02/2014	19/02/2014	2120	70	9769.965
123456	G0122446	02/04/2014	02/04/2014	5565	70	9769.965
123456	G0122457	02/04/2014	02/04/2014	265	70	9769.965
123456	-		-	0	70	9769.965

it's a pivot table and the problem is, that when it's closed i want to show the minimum date of a catalog number in the stock (in that case 19/02/2014)

the orders and the forecast don't have lot number, but still i get "-" as min date when i ask to calculate minimal date when there's lot and stock value. =if([Stock]>0 and Stock_Lot>0,min({$<Catalog_number=>}Date))

Catalog number	Date2	Stock	Orders	Forecast
123456	-	7950	70	9769.96

how can i present the first date in the table?

thanx

avner

[swuehl · Accepted solution]

Stock_Lot is not a number so it may be better to check with something like

=if([Stock]>0 and len(trim(Stock_Lot)),min({$<Catalog_number=>}Date))

This will not work if Stock and Stock_Lot are ambiguous in the context of your calculation, as in your example when using only dimension Catalo number.

Why not just try

=min(Date)

or

=min(if( len(trim(Stock_Lot)), Date))

resp.

=min({<Stock_Lot = {"*"}>} Date)

[#3]

Create a temperary field TempDate2 using any of the 2 methods described below

Method 1 :

Subfield(Date2,'/',3) & Subfield(Date2,'/',2) & Subfield(Date2,'/',1) AS TempDate2

OR

Method 2 :

Year(Date2) & Num(Month(Date2)) & Day(Date2) AS TempDate2

Now use the below expression :

=Only({<TempDate2={$(=MinString(TempDate2)),[Stock]={">0"},Stock_Lot={">0"}}>} Date2)

[Anonymous]

Tanks swuehl,

this sentence seems to work just fine

=min({<Stock_Lot = {"*"}>} Date)

all the best