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.
Thanks for reaching out. If you have the data about how much each day counts as a business day, then I think you can use the period to date functions we have in QuickSight. Have you tried using the functions listed here? Period to date computation - Amazon QuickSight.
Sean: Not sure how your suggestion addresses the original question. The link you provided is associated with autonarratives. I am attempting to present the results in a tabular or pivot visual.
@Bob Hmm this is a tricky one. Only way I can think of is to first make a separate lookup table which has 1 row for every single day of every year, and then another column which has how many weighted business days there are in that month. You could use QuickSight to help you create that lookup table and do the calcs in QS (if you want more info on what these calcs would look like I will include them below), but you will need to build a table visual and then export it to a file to be able to materialize those calcs so you can join them to your actual data. Weight:
You could also build this table manually or with SQL I would think. Nonetheless, once you have that table, you would join it to your actual data. What we do from here greatly depends on the type of visual you are trying to create and what type of math you want to do (for instance a KPI showing % Diff from most recent month vs same month last year, or something else…).
Each record in the dataset is an invoice with sales dollars as the metric and attributes (among many) for Area, Region, Store, Date, Invoice# AND the day weight for the date.
This would be a daily sales report for field operations. Each day would need to have the correct total of weight days for MTD, QTD and YTD or that date. The image is rough up of what I am doing with the visuals.
My data guy can tweak the feed for the data set to add/adjust fields for the day weights but it needs to be dynamic enough to update for a new day or if the user adjusts the time range if they are looking back at prior points in time.
With this in mind, would it make sense to have a MTD Weighted Days instead of a month total for weighted days. In other words, a record dated May 12, 2022 would have 9.25 ( the # of business days through that date) May 13 would have 10.25? Fields for QTD and YTD could be added rather easily.
If you have the the Weights for each day joined to you data, we should be able to calculated the number of weighted days MTD, QTD, YTD and MTD LY, QTD LY, YTD LY in QuickSight. Just tried some of them and I think they are working:
YTD Weighted Days: min(ifelse({order date}>=truncDate('YYYY', now()), Weight, 0), [{order date}])
Is showing me 117.25 through July 8 2022.
YTD LY Weighted Days: min(ifelse(dateDiff({order date}, now(), 'YYYY')=1 and {order date}<=addDateTime(-1, 'YYYY', now()), Weight, 0), [{order date}])
is showing me 119.5 through July 8 2021.
Thanks Jesse, I have the days calculating correctly but I am struggling to use it as the denominator in a calculated field. I’m not connecting some dots with this. Can you straighten me out here?
Neither of these work:
Comp Sales/Weighted Days or
sum(Comp Sales)/sum(Weighted Days)
Comp Sales is a calculated field.
Also, regarding the weighted days calc for a single store, is it possible to review the entire population of records for all stores and invoice dates to return the business days as opposed to just those invoices and dates for a single store? If a store does not have any invoices on a given business day the weighted day for that day will not be included in the total business days. I am not sure where management will fall on this so I would prefer to be able to address one way or the other.
Hey @Bob - can you paste the two formulas for 2020 Comp Sales and Curr YTD Business Days?
In general the you to either have both field aggregated or none aggregated. If one of them has a SUM in it, then you should wrap the other one in a SUM in the ratio calc.
Hello Bob,
Just checking in- I realize its been over 4 months since the last post, so just wanted to check and see if you needed further assistance with this issue?
Hi Bob! Since we have not received a response from you on this question, we are going to archive the question. If you still need this question answered, please create a new question topic. Thanks!
