Organization hierarchy - Company>Region>District>Store
Dataset contains header level for invoices over three years with store, date, invoice # with sale amounts and hierarchal attributes.
Dataset also contains a field in each record for the date’s value as a business day. Monday through Friday equal one business day each. Saturday is .25 of a business day and Sunday is 0. A non-holiday week equates to 5.25 business days.
April 2022 would be 22.25 business days and April 2021 would be 23 business days.
Need: Compare current YTD, QTD & MTD sales to same periods prior year dividing by the business days respective to those time frames to account for time frames that do not have the same number of business days. Visualize results at all levels of hierarchy. In other words divide total sales for a Region by the number of business days in a time period, for the month of April 2022 totals sales at all levels would be divided by 22.25 and total sales for April 2021 would be divided by 23.
I’ve stumbled through attempts with combinations of concat, rank and ifelse, I don’t think I have a solid enough grasp of things on QS yet to get where I need.
Any help appreciated.