Forum Discussion

Hulkoholic's avatar
Hulkoholic
New Member
1 year ago

visualize a month to month comparison

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_typeorder_datetimesold_quantitytotal_amount
    cashMonday,June 21,20211$10.51
    paypalMonday,June 21,20211$10.51
    cashTuesday,June 22,20212$21.02
    paypalTuesday,June 22,20212$21.02
    cashWednesday,June 23,20213$31.53
    cashWednesday,June 23,20211$10.51
    paypalWednesday,June 23,20211$10.51
    paypalWednesday,June 23,20211$10.51
    wiretransferThursday,June 24,20214$42.04
    cashThursday,June 24,20214$42.04
    wiretransferFriday,June 25,20212$21.02
    paypalFriday,June 25,20213$31.53
    wiretransferFriday,June 25,20211$10.51
    cashSaturday,June 26,20212$21.02
    wiretransferSunday,June 27,20216$63.06
    wiretransferSunday,June 27,20215$52.55
    cashMonday,June 28,20211$10.51
    paypalMonday,June 28,20211$10.51
    cashTuesday,June 29,20212$21.02
    paypalTuesday,June 29,20212$21.02
    wiretransferWednesday,June 30,20211$10.51
    cashWednesday,June 30,20213$31.53
    paypalThursday,July 1,20212$21.02
    cashThursday,July 1,20211$10.51
    paypalFriday,July 2,20214$42.04
    paypalFriday,July 2,20212$21.02
    wiretransferSaturday,July 3,20216$63.06
    cashSaturday,July 3,20216$63.06
    paypalSunday,July 4,20214$42.04
    cashMonday,July 5,20211$10.51
    wiretransferMonday,July 5,20211$10.51
    paypalTuesday,July 6,20212$21.02
    wiretransferWednesday,July 7,20213$31.53
    paypalWednesday,July 7,20213$31.53
    paypalThursday,July 8,20212$21.02
    wiretransferThursday,July 8,20211$10.51
    paypalFriday,July 9,20215$52.55
    paypalFriday,July 9,20215$52.55
    paypalSaturday,July 10,20213$31.53
    paypalSaturday,July 10,20212$21.02
    paypalSunday,July 11,20217$73.57
    cashMonday,July 12,20211$10.51
    wiretransferMonday,July 12,20211$10.51




1 Reply

  • KerKol's avatar
    KerKol
    Power Participant
    1 year ago

    Is this your school assignment / homework?