+1 800 256 3608 (toll-free in North America) or +49 7531 90 60 10| service@combit.com

Calculating based on dates

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.
image

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.