The file “exercise_data.xlsx” contains two sheets of data:
- “sales” which contains the sales for a single product sold by an e-shop:
- payment_type = the payment type of the order
- order_datetime = the date and time the order was placed
- sold_quantity = the number of units sold in that order
- total_amount = the total amount paid for those units
- “returns” which contains the number of items that have been returned from a specific order:
- datetime = the date and time the order was placed
- returned_quantity = the number of units which have been returned.
Exercise 1:
The owner of the shop wants to visualize a month to month comparison of the latest and last month, meaning June and July 2022, total amount from the sales sheet. However, given the very strong weekly seasonality of the business, syncing the 1st day of both months, 2nd day of both months, etc. would not give a true sense of the variation so instead, the owner would like to overlap the first Monday of the current month to the first Monday of the last month, etc…
Examples:
The chart above applies to July 2022 vs. June 2022
The chart above applies to March 2022 vs. February 2022
Please note that the current month ends later than the last month because there was no Tuesday, Wednesday and Thursday in the last week of February 2022, unlike in the last week of March 2022. The X axis has been hidden from the graph to not give away the solution.
- Please recreate the above graph
- The graph should be dynamic, responding to a slicer on months.
- Are there any alternative visualizations you would have used that would convey the same message?
- Attached is the test date , please refer the below.
payment_type order_datetime sold_quantity total_amount cash Monday,June 21,2021 1 $10.51 paypal Monday,June 21,2021 1 $10.51 cash Tuesday,June 22,2021 2 $21.02 paypal Tuesday,June 22,2021 2 $21.02 cash Wednesday,June 23,2021 3 $31.53 cash Wednesday,June 23,2021 1 $10.51 paypal Wednesday,June 23,2021 1 $10.51 paypal Wednesday,June 23,2021 1 $10.51 wiretransfer Thursday,June 24,2021 4 $42.04 cash Thursday,June 24,2021 4 $42.04 wiretransfer Friday,June 25,2021 2 $21.02 paypal Friday,June 25,2021 3 $31.53 wiretransfer Friday,June 25,2021 1 $10.51 cash Saturday,June 26,2021 2 $21.02 wiretransfer Sunday,June 27,2021 6 $63.06 wiretransfer Sunday,June 27,2021 5 $52.55 cash Monday,June 28,2021 1 $10.51 paypal Monday,June 28,2021 1 $10.51 cash Tuesday,June 29,2021 2 $21.02 paypal Tuesday,June 29,2021 2 $21.02 wiretransfer Wednesday,June 30,2021 1 $10.51 cash Wednesday,June 30,2021 3 $31.53 paypal Thursday,July 1,2021 2 $21.02 cash Thursday,July 1,2021 1 $10.51 paypal Friday,July 2,2021 4 $42.04 paypal Friday,July 2,2021 2 $21.02 wiretransfer Saturday,July 3,2021 6 $63.06 cash Saturday,July 3,2021 6 $63.06 paypal Sunday,July 4,2021 4 $42.04 cash Monday,July 5,2021 1 $10.51 wiretransfer Monday,July 5,2021 1 $10.51 paypal Tuesday,July 6,2021 2 $21.02 wiretransfer Wednesday,July 7,2021 3 $31.53 paypal Wednesday,July 7,2021 3 $31.53 paypal Thursday,July 8,2021 2 $21.02 wiretransfer Thursday,July 8,2021 1 $10.51 paypal Friday,July 9,2021 5 $52.55 paypal Friday,July 9,2021 5 $52.55 paypal Saturday,July 10,2021 3 $31.53 paypal Saturday,July 10,2021 2 $21.02 paypal Sunday,July 11,2021 7 $73.57 cash Monday,July 12,2021 1 $10.51 wiretransfer Monday,July 12,2021 1 $10.51
