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.
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 Quick Sight, 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.
@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.
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.
@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?
You can create the dataset with left joins in the Quick Sight 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.
Hi @shamitb,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
