Jump to content
Welcome to the new Spotfire community! Please see the sidebar announcement for more information on how to log in and get started! ×

How to calculate open and delinquent orders that occurred in a certain period in time?


Carlos Flores

Recommended Posts

I would like to show in a cross table by fiscal week the number of orders that were open at the beginning of the fiscal week (Monday) going back a year. My data consists of order number, create date, request date, and ship date. I would also like to show the number of delinquent open orders at the beginning of the same fiscal week (Monday).

FW202301 FW202302 FW202303

Open 132 145 115

Delinquent 51 34 62

Link to comment
Share on other sites

Hi Gaia, attached you will find an Excel file with sample data and their definitions. It also contains a sample cross table and chart that I want to create with Spotfire. Please let me know if you need any more info. Thank you in advance for your assistance.

Link to comment
Share on other sites

I am trying to understand the logic you want to apply.

The fiscal date can vary for different purposes. For instance, in the UK the personal tax fiscal year starts on April 6th.

So the first thing to do would be to create a fiscal 'equivalent' of each date, by rescaling the number of days of the actual start. In the case above, it would be 96 or 97 days depending on whether it is a leap year or not.

After that you can calculate the week of this rescaled date.

However, I don't understand how you define open orders. We have a Create Date, a Request Date and a Ship Date. If an order is only considered open when created, then you are missing all the orders requested but not created.

Then, you say you want the orders that are open at the beginning of the fiscal week. That further complicates matters, as I can maybe find a way to count the delinquent orders open during the week, not specifically on the Monday.

Anyway, to map the date to a rescaled fiscal date, I tried the following:

define a document property for the start day of the fiscal year, say 6. called fiscalYearDay

define a document property for the start month of the fiscal year, say 4, called fiscalYearMonth

define the fiscal week of the create date as:

Concatenate('FW',Year(DateAdd('dd',-DayOfYear(Date(Year([Create Date]),${fiscalYearMonth},${fiscalYearDay})),[Create Date])),Week(DateAdd('dd',-DayOfYear(Date(Year([Create Date]),${fiscalYearMonth},${fiscalYearDay})),[Create Date])))

where I am creating the number of days to the fiscal start as

DayOfYear(Date(Year([Create Date]),${fiscalYearMonth},${fiscalYearDay}))

and then I am subtracting it from the real [ Create Date] to obtain the 'fiscal' [Create Date]

then I am concatenating FW, the resulting year and the resulting week.

If you do that for each of your dates, you should end up with a fiscal week for each date.

I am not sure the calculations are correct. Please compare to your own.

To go further, please can you elaborate on the exact requirements?

Link to comment
Share on other sites

  • 4 weeks later...

Hi Gaia, thank you for the information.  I'm not sure what happened to my earlier reply, I've added more detail and reposted it.

 

Let me share more details regarding the logic I'm trying to use to create the Spotfire report.

Regarding the Fiscal Weeks, they start on a Monday and end on the Sunday.  I'm using the simple Spotfire formula below to get the string format "FWyyyyww":

   "FW" & YearAndWeek([Create Date])

For the Monday Date of the Fiscal Week, I'm using the following Spotfire formula:

   CASE DAYOFWEEK([Create Date]) 
      WHEN 0 THEN DATEADD('day', (-DAYOFWEEK([Create Date])) - 6, [Create Date]) 
      ELSE DATEADD('day', (-DAYOFWEEK([Create Date])) + 1, [Create Date]) 
   END

For example, for today's date, January 18th, 2024, the FW value would be "FW202403" and the Monday Date of the Fiscal Week would be "1/15/2024".

Below are the MS Excel formulas that I used to calculate the Closed, Open, and Delinquent orders:

   Closed: IF(TODAY() >= MondayDateOfFiscalWeek + 7, COUNTIFS(ShipDate >= MondayDateOfFiscalWeek, ShipDate < MondayDateOfFiscalWeek + 7), "")

   Open: IF(TODAY() >= MondayDateOfFiscalWeek + 7, SUM(COUNTIFS(CreateDate < MondayDateOfFiscalWeek + 7, ShipDate = ""), COUNTIFS(CreateDate < MondayDateOfFiscalWeek + 7, ShipDate >= MondayDateOfFiscalWeek + 7)), "")

   Delinquent: IF(TODAY() >= MondayDateOfFiscalWeek + 7, SUM(COUNTIFS(CreateDate < MondayDateOfFiscalWeek + 7, ShipDate = "", RequestDate < MondayDateOfFiscalWeek + 7), COUNTIFS(CreateDate < MondayDateOfFiscalWeek + 7, ShipDate >= MondayDateOfFiscalWeek, RequestDate < MondayDateOfFiscalWeek + 7)), "")

The logic used for the above formulas is:

   The TODAY() >= MondayDateOfFiscalWeek + 7 criteria in the beginning of the formulas is just to show the calculation results for only the completed fiscal weeks year-to-date and not partial or future ones.  Blanks should be shown for partial or future fiscal weeks.

   Closed: I want to know the number of orders that were shipped/closed during the current fiscal week, from Monday to Sunday, so the formula is counting the number of orders that have their Ship Date between the Monday of the fiscal week, including that Monday, and before the Monday of the next fiscal week.

   Open: It's an order that was created/opened before or during the current fiscal week and has not shipped/closed yet as of the end of the current fiscal week.  The formula is adding two counters, the 1st one counts the number of orders that were opened before or during the current fiscal week (before the Monday of the next fiscal week) and still remain open to that day, have not been shipped.  The 2nd one counts the orders that were also created before or during the current fiscal week and if they were shipped, had to be shipped on the Monday of the next fiscal week or beyond.  It's like a snapshot of the open status at the end of the current fiscal week.

   Delinquent: The Request Date is when does the customer want to have the order shipped, it's basically fixed for now to 45 days after the order was created/opened, it's like a target ship date.  It's an open order, same definition as above, but its Request Date has already past.  It's basically an open order that is already late to be shipped based on its Request Date.  Same as the Open Order formula, it's adding the same two counters, but including the clause for both that the Request Date should be less than the end of the current fiscal week (before the Monday of the next fiscal week).  Same as above, it's a snapshot of the delinquent status at the end of the current fiscal week.

The two challenges that I have are:

   1) How can I create a Spotfire Combination Chart and Cross Table with data composing on two dates, Create Date and Ship Date, on the same axis (X-axis and Horizontal Axis, respectively).  Also, if there are no Create or Ship dates at all for a particular fiscal week, how can I still show all 52 fiscal weeks in 2024 in both the Combination Chart and Cross Table?

   2) In the expressions, how can I reference the Monday Date for the Fiscal Week (X-axis and Horizontal Axis) to be able to compare to the Create, Request, and Ship dates of all the orders for both the Combination Chart and Cross Table.  For example, the Spotfire expressions for Closed, Open, and Delinquent orders should be:

   COUNT(CASE WHEN (([Ship Date] >= [MondayDateOfFiscalWeek]) AND ([Ship Date] < DATEADD('day', 7, [MondayDateOfFiscalWeek]))) THEN 1 ELSE NULL END) AS [Orders Closed During the Fiscal Week]

   COUNT(CASE WHEN (([Create Date] < DATEADD('day', 7, [MondayDateOfFiscalWeek])) AND (([Ship Date] IS NULL) OR ([Ship Date] >= DATEADD('day', 7, [MondayDateOfFiscalWeek])))) THEN 1 ELSE NULL END) AS [Open Orders at the End of the Fiscal Week]

   COUNT(CASE WHEN (([Create Date] < DATEADD('day', 7, [MondayDateOfFiscalWeek])) AND (([Ship Date] IS NULL) OR ([Ship Date] >= DATEADD('day', 7, [MondayDateOfFiscalWeek]))) AND ([Request Date] < DATEADD('day', 7, [MondayDateOfFiscalWeek]))) THEN 1 ELSE NULL END) AS [Delinquent Orders at the End of the Fiscal Week]

but what should I use to reference [MondayDateOfFiscalWeek]?

Please let me know if you need any additional information.  Thanks again for your time.

Link to comment
Share on other sites

  • 2 weeks later...

Thank you for the additional information, It is likely to have got lost during the Community switch.

This is my understanding:

  • MondayDateOfFiscalWeek as you calculated it is relative to the Create Date.
  • In your calculation, there is no special meaning of fiscal week, as the MondayDateOfFiscalWeek is just the date of the Monday of that week.

In what follows, today's date is calculated as Date(DateTimeNow()). 

My suggestion is to add the following three calculated columns. If the formulas are not 100% correct, I hope the idea helps.

Closed
There is a Ship Date entered, and the Ship Date is in the same fiscal week as the Create Date:

([Ship Date] is not null) and ([Ship Date]>=[MondayDateOfFiscalWeek]) and ([Ship Date]<DateAdd('dd',7,[MondayDateOfFiscalWeek]))

Open:
It was not closed yet, but
today's date is still within the same week as the Create Date, so there is still time for the ticket not to become Delinquent:

([Closed]=False) and (Date(DateTimeNow()) <DateAdd('dd',7,[MondayDateOfFiscalWeek]))

Delinquent:
It was not closed yet, and
today's date is past the same fiscal week, so not the ticket has become delinquent

([Closed]=False) and (Date(DateTimeNow())>=DateAdd('dd',7,[MondayDateOfFiscalWeek]))

So now we have three masks to apply to the data, and you can add counts to the cross table.

Link to comment
Share on other sites

  • 2 weeks later...

Hi Gaia, thanks again for your feedback.

I tried implementing your suggestion, but I still have a couple of issues.  In your formulas, my understanding is that MondayDateOfFiscalWeek is a Calculated Field that is based on the Create Date of the order in each row.  When I implement your three formulas from above, my thought is that these metrics shouldn't be static, they should be dynamic based on the date of the Monday of each week along the X or Horizontal Axis, so there shouldn't be Calculated Fields, they should be Custom Expressions for the Y-axis (Combination Chart) or for the Cell Values (Cross Table).  Which brings me to my second issue, how should I reference the date of the Monday of each week along the X or Horizontal Axis.

For other metrics, in which I don't have to actually use the date in the X or Horizontal Axis, I just use the expressions OVER (LastPeriods(NumberOfPeriods, [Axis.X])) or OVER (LastPeriods(NumberOfPeriods, [Axis.Columns])).  These expressions will not work in this case since I don't know the number of periods going back, it's based actually on date comparisons.

I hope I explained myself better.  Please let me know if you need anything else to find a solution to these issues.  Thanks!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...