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

[Carlos Flores]

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

[Gaia Paolini]

can you provide a sample csv?

[Carlos Flores]

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.

[Carlos Flores]

Hi Gaia, attached you will find a csv file with sample data. Thanks!

[Gaia Paolini]

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?

[Carlos Flores]

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.

[Gaia Paolini]

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.

[Carlos Flores]

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!

[Gaia Paolini]

Hello, this issue strongly depends on how you want to configure and visualize your data. Without an anonymised dxp example, I cannot work out exactly what the requirements and the obstacles are.

[Carlos Flores]

Orders with Table and Chart.xlsx

Hi Gaia, I have attached an Excel file with anonymized data and formulas, as well as a table and a chart that I want to create in Spotfire (cross table and combination chart).  Thank you!

[Gaia Paolini]

I am trying to look at the data. I can kind of see some calculations but not  a column for FW or the Monday of the fiscal week.
Looking at the results, focusing on the count of Closed orders.

FW202301: first week in Jan

   I see four orders (426013-16). of 1st and 2nd of January 2023. Two have a Ship date of March or May, two do not have a Ship Date. The excel sheet says 2 closed within fiscal week.

For FW202303, second week in Jan,

  I can see 9 orders (426156-426164) , all of them have a Ship date, none sooner than Feb. none of them closed within the week. The excel sheet says 7 closed within fiscal week.

Have I misunderstood the definitions?
My definition for Closed would be this, which is close to the one you shared apart from adding a clause that Ship Date is present and expressing the result as Boolean rather than Integer:

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

Yours: 
CASE WHEN (([Ship Date] >= [MondayDateOfFiscalWeek]) AND ([Ship Date] < DATEADD('day', 7, [MondayDateOfFiscalWeek]))) THEN 1 ELSE NULL END

 

[Olivier Keugue Tadaa]

Hello Carlos,

I have a suggestion based on a different approach to create this calculations in Spotfire. Indeed the first mistake is that you have created your Fiscal Week based on the create date and therefore making it impossible to actually calculate the related indicators since they are all linked to the previous date dimension based on create date. Another approach consists in first creating your fiscal period dimension from another data source and then perform the calculations from the Orders table. This is exactly what you've done in your Excel example.

With this said here is the proposal

1- create a data entry with fiscal weeks with the following two columns FiscalWeek and MondayDateOfFiscalWeek 

2- insert calculations from the Orders table 

The attached example shows how you can  compute the Closed value. You can follow the same principle but for the "Open" you will maybe need a script since inserting column from a table doesn't allow joining tables based on "inequalities" criteria. The below screenshot gives an overview.

Orders.dxp

Edited March 7 by Olivier Keugue Tadaa

[Carlos Flores]

Hi Gaia and Olivier, thank you for your feedback!

Hi Olivier, yes, the Fiscal Week issue was the challenge # 1 that I identified above and wasn't sure how to approach it.  Thanks for the attached example, but unfortunately, my company hasn't upgraded the Spotfire version to 12.5 let alone to 14.0 or 14.1, so I wasn't able to open your dxp file.  Would it be possible to save the example as a 12.0.2 or lower version and share it so I can open it and check out your logic?

Per your screenshot, the "Closed" columns match exactly the "Orders Closed During FW" green columns from my Excel file.  Could you please also include in your example the script that I should use for the "Open Orders at End of FW" and "Delinquent Orders at End of FW" so they can also match the blue and red lines in the chart from my Excel file?  Thank you again.

[Olivier Keugue Tadaa]

Hi Carlos

Happy to hear this approach looks promising to you. I've attached a DXP v12.
I'll look if I can create some scripts for the two other calculations (Open and Delinquent) but meanwhile I've done something that I hope could be a solution.

- Open : Sum([PreviouslyCreated]) - Sum([Previously closed]) as [Opened] : this as calculating the all previously created orders minus the all previously closed orders.

where :

- [PreviouslyCreated]  is a calculated column defined as  : Sum([Orders Created]) OVER (AllPrevious([FiscalWeek])) - [Orders Created]. : this is calculating all orders created before the current fiscal week

- and [Previously closed] is a calculated column defined as  : Sum([Closed]) OVER (AllPrevious([FiscalWeek])). : this is calculating all the orders closes before the current fiscal and during this fiscal week

- Delinquent :  Sum([PreviouslyCreated]) - Sum([Previously closed]) - Sum([Orders Requested]) as [Delinquents] : these are the opened orders minus the ones that are requested this fiscal  week (I'm not sure with this last formula)

 

The columns [Closed] , [Orders Created], [Orders Requested] are calculated using the approach described above.

But you'll have everything in the attached DXP.

See below an example result and if it suits to you, we won't need additional scripting.

Orders_12.dxp

[Carlos Flores]

Attached you will find an Excel file with the Fiscal Week numbers and their start dates (Monday date of fiscal week) from 2015 to 2030.

FW Numbers and Start Dates.xlsx

[Olivier Keugue Tadaa]

Thanks Carlos, were you able to open the DXP I sent yesterday ? And if yes is it a potential solution for you ?