visualize a month to month comparison

[Hulkoholic]

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

  
  
  
  
  

[KerKol]

Is this your school assignment / homework?