Fiscal Weeks Aggregate to Calendar Month - Formula?
Ivan Jovanovic Apr 6, 2018 11:35 AMIt seems that I have a problem a bit opposite to similar ones here on forum regarding fiscal and calendar issues. My data set is on fiscal week level, meaning that each row contains metrics within one fiscal week from Saturday to Friday. Date within the dataset is the Last day of the fiscal week (Friday). When I make my tables and graphs showing weekly data I can drill up – to monthly, quarterly and yearly level. But what I need is a formula, I guess, that will allow me to see data on calendar month level for all levels of data aggregation except the lowest one – which is fiscal week. Is this possible?
Currently, what Tableau does by default it simply sums all weeks that contain a particular month (number) within the Date so that when data aggregates to month level I get that sum which, of course is not a calendar week. Is it possible to make a formula that would proportionally distribute a fiscal week’s data depending on number of days within a week which expands over two consecutive months (last month end and following month start)? I have attached an example of data, and here is an example of what exactly I want to achieve:
Week End Day | Metric Count |
1/26/2018 0:00 | 11382 |
2/2/2018 | 10935 |
2/9/2018 | 10220 |
2/16/2018 | 9364 |
2/23/2018 | 9588 |
3/2/2018 0:00 | 8950 |
Currently, count for month of February is 40107 (2/2, 2/9, 2/16 and 2/23 summed). Data is showing last week day, this meaning that this monthly total encompasses 5 days from January (27-31 Jan) and excludes 5 days from February (24-28 Feb)
I want my count to be from the first to the last day of the month (calendar month). So i.e week 2/2 has only 2 days in February, therefore (10935/7)*2 is sum that should be taken into account; adding weeks 2/9, 2/16, 2/23 and 5 days from week 3/2 (8950/7)*5. Total sum for February would be 49751.86. So I need a formula that will always count the days within a week that transitions from one month to the following one and proportionally distributes the metric on an average daily level in order to sum it all for the whole month. I welcome other ideas of course
Thank you!
Ivan