Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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)
Tanks swuehl,
this sentence seems to work just fine
=min({<Stock_Lot = {"*"}>} Date)
all the best