Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Show Minimum Date

Hi All,

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

 Date2 19/02/2014 02/04/2014 02/04/2014 - Catalog number Stock_Lot Date1 Stock Orders Forecast 123456 G0121761 19/02/2014 2120 70 9769.965 123456 G0122446 02/04/2014 5565 70 9769.965 123456 G0122457 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

1 Solution

Accepted Solutions
MVP

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 Replies
MVP

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)

Not applicable
Author

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
Not applicable
Author

Tanks swuehl,

this sentence seems to work just fine

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

all the best

Community Browser