Get Number of Orders and Fulfilled in a Day

[shamitb]

Hi, I need to build a report, where I have 4 colums. OrderId, Order Date, Fulfillment Date, Product.

For each order Id there can be different date of order and fulfillment date. I need to create a table visual showing. In each week, how many orders were placed and fulfilled. Here is the example of the table.

OderId	OrderDate	Fullfilmentdate	Product
X001	1/1/2025	1/3/2025	Electronics
X002	1/1/2025	1/3/2025	Books
X003	1/1/2025	1/3/2025	Electronics
X004	1/1/2025	1/3/2025	Electronics
X005	1/3/2025	1/5/2025	Electronics
X006	1/3/2025	1/5/2025	Electronics
X007	1/3/2025	1/5/2025	Books
X008	1/4/2025	1/6/2025	Electronics
X009	1/4/2025	1/6/2025	Electronics
X010	1/4/2025	1/6/2025	Books
X011	1/5/2025	1/7/2025	Fashion
X012	1/5/2025	1/7/2025	Fashion
X013	1/5/2025	1/7/2025	Electronics
X014	1/7/2025	1/9/2025	Books
X015	1/8/2025	1/10/2025	Books
X016	1/8/2025	1/10/2025	Books
X017	1/8/2025	1/10/2025	Electronics
X018	1/9/2025	1/11/2025	Fashion
X019	1/9/2025	1/11/2025	Books
X020	1/9/2025	1/11/2025	Books
X021	1/9/2025	1/11/2025	Electronics
X022	1/10/2025	1/12/2025	Fashion
X023	1/11/2025	1/13/2025	Fashion
X024	1/12/2025	1/14/2025	Fashion
X025	1/12/2025	1/14/2025	Fashion
X026	1/12/2025	1/14/2025	Books
X027	1/13/2025	1/15/2025	Books
X028	1/14/2025	1/16/2025	Fashion
X029	1/14/2025	1/16/2025	Books
X030	1/15/2025	1/17/2025	Fashion
X031	1/15/2025	1/17/2025	Books
X032	1/15/2025	1/17/2025	Fashion
X033	1/15/2025	1/17/2025	Books
X034	1/16/2025	1/18/2025	Electronics
X035	1/16/2025	1/18/2025	Books
X036	1/16/2025	1/18/2025	Electronics
X037	1/16/2025	1/18/2025	Electronics
X038	1/16/2025	1/18/2025	Electronics
X039	1/16/2025	1/18/2025	Fashion
X040	1/17/2025	1/19/2025	Electronics
X041	1/17/2025	1/19/2025	Electronics
X042	1/17/2025	1/19/2025	Fashion
X043	1/18/2025	1/20/2025	Electronics
X044	1/18/2025	1/20/2025	Books
X045	1/18/2025	1/20/2025	Electronics
X046	1/19/2025	1/21/2025	Books
X047	1/19/2025	1/21/2025	Books
X048	1/20/2025	1/22/2025	Electronics
X049	1/20/2025	1/22/2025	Fashion
X050	1/20/2025	1/22/2025	Fashion
X051	1/21/2025	1/23/2025	Electronics
X052	1/21/2025	1/23/2025	Books
X053	1/21/2025	1/23/2025	Fashion
X054	1/21/2025	1/23/2025	Books
X055	1/21/2025	1/23/2025	Electronics
X056	1/22/2025	1/24/2025	Electronics
X057	1/22/2025	1/24/2025	Fashion
X058	1/22/2025	1/24/2025	Books
X059	1/22/2025	1/24/2025	Fashion
X060	1/22/2025	1/24/2025	Electronics
X061	1/23/2025	1/25/2025	Books
X062	1/24/2025	1/26/2025	Electronics
X063	1/24/2025	1/26/2025	Books
X064	1/24/2025	1/26/2025	Electronics
X065	1/25/2025	1/27/2025	Fashion
X066	1/26/2025	1/28/2025	Fashion
X067	1/26/2025	1/28/2025	Fashion
X068	1/26/2025	1/28/2025	Books
X069	1/27/2025	1/29/2025	Electronics
X070	1/27/2025	1/29/2025	Electronics
X071	1/28/2025	1/30/2025	Books
X072	1/28/2025	1/30/2025	Electronics
X073	1/29/2025	1/31/2025	Books
X074	1/29/2025	1/31/2025	Electronics
X075	1/30/2025	2/1/2025	Electronics
X076	1/30/2025	2/1/2025	Books
X077	1/31/2025	2/2/2025	Electronics
X078	1/31/2025	2/2/2025	Fashion
X079	1/31/2025	2/2/2025	Electronics

[lary_andr]

hello @shamitb hope this message finds you well.

To create a report in qs that displays the number of orders placed and fulfilled per week, start by ensuring your data is in an appropriate format, such as a CSV file or connected to a supported source like a database or S3. Make sure the OrderDate and FulfillmentDate columns are formatted as dates. In QuickSight, create a new dataset by importing your data and verifying that the date columns are correctly recognised. Begin a new analysis and select the dataset. Add a bar or line chart to the analysis, dragging OrderDate to the X-axis and grouping it by week, and OrderId to the Y-axis, configuring it to count the number of orders. Repeat the process for FulfillmentDate to show fulfilled orders. Customise the chart by adjusting titles, labels, and colours to enhance clarity. If needed, add filters or parameters to refine the data further. Once the report is complete, publish and share it with the relevant stakeholders. This approach ensures a clear and concise visualisation of weekly order and fulfilment trends in qs.

[shamitb]

@lary_andr Thank you for your quick response. The problem is that first I am not able to bring them on the same chart as in X axis I can only bring either Order date or Fulfillment date. I will end up creating two different line graphs. I would like to bring either in a single table table or graph. For e.g. it has to be 1 column for the weeks and 2nd column total order and 3rd column total Fulfillment.

[Giridhar.Prabhu]

Hi @shamitb

Ideally, you should have a separate Calendar table and have a two left joins to your transaction table (Calendar Date = Order Date) and (Calendar Date = Fulfillment Date) and then do an aggregation of the count of Order Id by Calendar Week, Product etc.

This is easily achievable if you are doing this in SQL.

Regards,
Giri

Regards,
Giri

[shamitb]

@Giridhar.Prabhu

Ideally, you should have a separate Calendar table and have a two left joins to your transaction table (Calendar Date = Order Date) and (Calendar Date = Fulfillment Date) and then do an aggregation of the count of Order Id by Calendar Week, Product etc.

This is easily achievable if you are doing this in SQL.

Regards,

@Giridhar.Prabhu , Are you advising me to do this at the SQL Level instead of something inside the QS dataset? Unfortunately, at present unable to make this change at the SQL, is there a solution that that can be achieved on QS?

[Giridhar.Prabhu]

Hi @shamitb,

You can create the dataset with left joins in the QuickSight dataset itself. The only extra thing you need is a separate calendar table which has Date, Week, Qtr, Year as columns so you can aggregate any way you want.

Analysis:

Dataset:

Excel File:

Regards,
Giri