3 Replies Latest reply on Apr 11, 2018 11:04 PM by Eric Hammond

    Fiscal Weeks Aggregate to Calendar Month - Formula?

    Ivan Jovanovic

      It 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
        0:00

      10935

      2/9/2018
        0:00

      10220

      2/16/2018
        0:00

      9364

      2/23/2018
        0:00

      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

        • 1. Re: Fiscal Weeks Aggregate to Calendar Month - Formula?
          Eric Hammond

          Hi Ivan,

           

          One approach to consider: Create a date reference table (or view) that lists each calendar date along with the Friday of the week in which it falls:

           

          CalDate         FriDate

          2/21/2018      2/23/2018

          2/22/2018      2/23/2018

          2/23/2018      2/23/2018

          2/24/2018      3/2/2018

          2/25/2018      3/2/2018

          2/26/2018      3/2/2018

          etc.

           

          Join to this table ON [Week End Day] = [FriDate].  Display CalDate on the worksheet to summarize by calendar month/quarter/year.  Divide all metric counts by seven to adjust for the extra records created by the join.

          • 2. Re: Fiscal Weeks Aggregate to Calendar Month - Formula?
            Ivan Jovanovic

            Thank you very much Eric,

             

            I see what you mean here - it is practically making a daily view and then summarizing the data.
            The only thing is that I do not know how to create a data reference table in Tableau. I am not allowed to connect any external data source to my workbook, meaning that I would need a formula that repeats each FriDate 7 times and for each row an additional row (CalDate  column) would be calculated as a day of that FriDate week (days being 0, 0+1,0+2,0+3..0+6 i.e). Any idea how to write this calculation?

             

            Thank you for your help, much appreciated.

            Ivan

            • 3. Re: Fiscal Weeks Aggregate to Calendar Month - Formula?
              Eric Hammond

              Hi Ivan,

               

              Is your data source one that allows custom SQL (such as SQL Server)?  If yes, then you can join the first table to custom SQL, and the custom SQL doesn't need to reference any other tables to create the necessary list of dates.  If this is an option then I can suggest some SQL.