Find number of shipments after a duration increment

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
I have in my db a number of shipments to customers. I want to be able to automatically count - or at least identify - only those shipments that occur after a rolling duration increment.

Taking a year as that example:
- When a customer's first shipment is less than a year old, I want to count all their shipments.
- When a customer's first shipment is more than 1 but less than 2 years old, I want to count all their shipments that occurred after the first full year since their first shipment (i.e. in the second year).
- When a customer's first shipment is more than 2 but less than 3 years old, I ant to count all their shipments that occurred after the second full year since their first shipment (i.e. in the third year).

I don't want to use calendar years, but rather rolling increments from when the customer first signed up.

Any help is much appreciated!

Photo of Stuart

Stuart

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of KenFirch

KenFirch

  • 10 Points
If I understand this correctly, you'll need to add a summary field to your Customer / Shipment relationship that is the Minimum of your Shipping Date, I'll call that Minimum Shipment Date. Or whatever you already have for the "customer first signed up" date. Then add a Lookup field to your Shipment table to this new Minimum Shipment Date field and call that First Shipment Date. Now add 3 Formula numeric fields to your Shipment table:

Year 1 Shipments: If([Shipment Date] >= [First Shipment Date]
and [Shipment Date] < AdjustYear([First Shipment Date], 1), 1,0)

Year 2 Shipments: If([Shipment Date] >= AdjustYear([First Shipment Date], 1)
and [Shipment Date] < AdjustYear([First Shipment Date], 2), 1,0)

Year 3 Shipments: If([Shipment Date] >= AdjustYear([First Shipment Date], 2)
and [Shipment Date] < AdjustYear([First Shipment Date], 3), 1,0)

Then add 3 Summary fields that Total the Year 1 Shipments values, etc.