Hi there,
I am wondering if anyone can help me sort out how to calculate a few values related to shelf life?
In SQL Server I use the following 2 formulas to calculate % of shelf life left and the date at which the material reaches 75% remaining (this is a customer driven cutoff for usage)
cast(((History.ExpiryDate-History.ProductionDate)-(getdate()-History.ProductionDate))as float) / nullif(cast((History.Expirydate-History.ProductionDate)as float),0)as ‘% Left’
cast(((ExpiryDate-ProductionDate))as float)*.75+ProductionDate as ‘25% Date’
I have been trying for several hours but can’t seem to figure out how to do this in the version of Combit (List & Label 21), which is built into our Power Pick Global software.
Hello,
I’m not sure what exactly the step is which is confusing you inside of the List & Label Designer. Maybe it seems to be the problem calcualting with date values. For that you can easily use the built-in formula assistent and its powerful date functions like DateDiff (calcs the difference between tow given dates as days) or DateToJulian (returns a given date as in julian format).
Not exactly know the possible values of your data it could be the following formula in the Designer:
/* cast(((ExpiryDate-ProductionDate))as float)*.75+ProductionDate as ‘25% Date’ */
JulianToDate(((DateToJulian(Orders.RequiredDate) - DateToJulian(Orders.OrderDate)) * 0.75) + DateToJulian(Orders.ShippedDate))
Or
AddDays(Orders.ShippedDate, DateDiff(Orders.OrderDate,Orders.RequiredDate) * 0.75)
Maybe this could help you in order to get ahead.
I keep running into conversion issues
for example to get the dates to subtract I have to do
ToString$(History.Expirydate-History.productiondate)
but as soon as I add in the *.75 it no longer works since it says the left side is supposed to be number
I modified one of your examples to use the appropriate field names, this is what i get
This is an example of what I am trying to pass to a label, the last 2 columns need to be calculated on the label since they are not stored in the underlying table
| Lot |
Production Date |
Expiry Date |
Shelf Life Years |
% Left |
25% Date |
| IMP-03072014 |
8/24/2006 |
8/24/2021 |
15 |
15.6% |
11/23/2017 |
Both Production Date and Expiry Date are Datetime fields in SQL Server.
If I try your second example I get this.
I got the second example to work using
DateDiff(History.Expirydate,History.Productiondate) *.75 + Tonumber(History.Productiondate)
I got my first example working finally
ToNumber(DateDiff(History.Expirydate,History.Productiondate)-(Today()-History.Productiondate))/ToNumber(History.Expirydate-History.Productiondate)
Great, that are good news. Of course it is depending on the type of the field in List & Label how each value could be calculated - and they have matching the formula parameters and return values for doing this.
