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.
