Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead 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.

Catalog numberStock_LotDate1Date2StockOrdersForecast
123456G012176119/02/201419/02/20142120709769.965
123456G012244602/04/201402/04/20145565709769.965
123456G012245702/04/201402/04/2014265709769.965
123456--0709769.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 numberDate2StockOrdersForecast
123456-795070

9769.96

how can i present the first date in the table?

thanx

avner

1 Solution

Accepted Solutions
swuehl
MVP
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)



View solution in original post

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