search

bezlio / bezlio-gallery

Collection of bezls that you can import and use in your Bezlio account
MIT License
3 stars 0 forks source link

Shipping Manager - On Time Shipments - Spark Box #34

Open aellis opened 6 years ago

aellis commented 6 years ago

Create an on time shipments gallery item similar to Comparative Sales Breakdown. The query spec is as follows:

o Variations: Year to date, month to date o Description: This query should return the on time shipping performance for the respective period and a comparison to the previous period. So MTD would be MTD this year and also a row for MTD last year. See mtd-sales-cur-vs-prior-by-region.txt and ytd-sales-cur-vs-prior-by-region.txt ytd-sales-cur-vs-prior-by-region.txt mtd-sales-cur-vs-prior-by-region.txt

for guidance on the approach. An on time shipment should be defined as one where the actual ship date is on or before the desired ship date at the line or delivery schedule / release level.

The big numbers on the spark box should be the current values and the trends below should be the previous corresponding period.

aellis commented 6 years ago

Updated query to conform to standards specified to Raman:

OnTimeShipment.txt

bitingmidge commented 6 years ago

@aellis What should the data structure of the out-of-the-box Excel spreadsheet look like? I would think it would have columns such as "Shipment ID", "Requested Date", "Ship Date", etc...

However, the attached OnTimeShipment.txt query already has everything rolled up into just three fields: "STATUS", "COUNT", and "YEAR". In order to make this easily switchable between data sources wouldn't that mean that the Excel spreadsheet would need to have just those three same fields as well? If so, not sure how we can do both YTD and MTD with just those three fields?

aellis commented 6 years ago

@bitingmidge I think we could still make this work with the Excel workbook simply by adding a new sheet with formulas that do the same sort of roll-up. I was somewhat tempted to say just make the queries not roll-up and be more raw data like you suggested, but I think some companies with massive number of shipments might see this Bezl perform poorly if they had to do all of that data transfer.