Problem with subexpressions.

EAHendrix · 02-28-2023 02:06 PM

I am trying to calculate a the number of storage days that have elapsed in an inventory application. My problem is that it depends on multiple dates.

If item is still in stock then the number of days needs to be calculated from the date it was delivered till todays date. If the item has been picked up then it needs to be calculated between the delivery date and the shipping date.

That part was easy enough, but now the customer wants to share the storage time with their distributors. So if there is a date in the distributor column then it needs to calculate from that date.

I tried the following, but that doesn't work, any suggestions?

IFS(
AND(ISBLANK([distributor Date]),(ISBLANK([shipDate])),(HOUR(TODAY()-[deliveryDate])/24)+1),
AND(ISBLANK([distributor Date]),(ISNOTBLANK([shipDate])),(HOUR([shipDate]-[deliveryDate])/24)+1),
AND(ISNOTBLANK([distributor Date]),(ISNOTBLANK([A_Date])),(HOUR([distributorDate]-[deliveryDate])/24)+1),
)

Swoopy

try,

IFS(
   AND(ISBLANK([distributor Date]), (ISBLANK([shipDate])),
      (HOUR(TODAY()-[deliveryDate])/24)+1,
   AND(ISBLANK([distributor Date]),(ISNOTBLANK([shipDate])),
      (HOUR([shipDate]-[deliveryDate])/24)+1,
   AND(ISNOTBLANK([distributor Date]),(ISNOTBLANK([A_Date])),
      (HOUR([distributorDate]-[deliveryDate])/24)+1
)

or,

IFS(
   AND(ISBLANK([distributor Date]), (ISBLANK([shipDate])),
      (HOUR(TODAY()-[deliveryDate])/24)+1,
   AND(ISBLANK([distributor Date]),(ISNOTBLANK([shipDate])),
      (HOUR([shipDate]-[deliveryDate])/24)+1,
   TRUE,
      (HOUR([distributorDate]-[deliveryDate])/24)+1
)

Marc_Dillon

Can you assume that delivery date < ship date < distributor date?

If so, you could just do:

TODAY() - MAX( LIST( [deliver] , [ship] , [dist] ) )

EAHendrix

the distributor date will be less than the ship date, but more than the deliver