3 Replies Latest reply on Jul 23, 2018 8:30 AM by swaroop.gantela

    Desperately need help calculating rolling sales by week and returning max values of a sum

    Nicholas Zabilski

      I have attached an example .twbx file as well as the excel data and a censored final product of what the report will look like.

       

      Summary of what I want: 1) To have tableau calculate a rolling season to date sales figure by style # and by week #

                                               2) For each week and style, take the rolling sales figure, add it to Store inventory and Warehouse inventory to get a total investment figure.

                                               3) Find the maximum total investment figure so I can use my sell thru formula to return "% sold"  (Current season to date sales / Total investment)

       

       

      The closest I have been able to get to this in tableau is to use table calculations...which unfortunately isn't possible since aspects of the table calculation cant be brought into the view (like week # since the report is an aggregate season to date view.)

       

      I want to do the above math in order to get as close as I can to the total amount of inventory that was available throughout the time period of my data set. This breaks out to: Every unit we have sold + all the units in stores + all the units in the warehouse. I cannot simply use current season to date sales and current inventory, since we have had a few poor selling styles transferred out of the system that will not show up in the current weeks information. I essentially want to find the high water mark for each style number throughout my data, and use that figure when computing my Sell thru Percentage.

       

      For now, my (time consuming and very manual) solution has been:

      1) Update my data set with the previous weeks sales data

      2) Export sales for each style # by week into excel and create a rolling sales column

      3) Update tableau with this rolling sales information

      4) In the tableau sheet from step 2, bring in the newly created rolling sales, store inventory and warehouse inventory

      5) Export the above into excel

      6) Sum up rolling sales, store inventory, and warehouse inventory

      7) Use the Maxif formula with the criteria being style # and the max range being the sum calculated above. This column is named "TTL Invest"

      8) update tableau with the new TTL Invest figure

       

      Obviously this is incredibly time consuming and usually takes up most of my morning since each tableau update takes about 15 minutes. My Monday mornings are getting filled up and this report is REALLY dragging me down so I would appreciate any insight on how to get this as automated as possible.

       

      A couple of things to note... The week # is misleading because it actually spans two seasons. Weeks 44-53 were actually from last season and 1-23 are the current season. For the purpose of this report I am using the whole range, so starting at week 44 and ending at week 23. I made the calculated field "multi season week" to change 44 into -9 and 53 into 0 so I could arrange in chronological order.

       

      Thank you for reading this far, I'm sure I could explain things better, so please let me know if any clarification is needed.

       

      Thanks agian!!!!

        • 1. Re: Desperately need help calculating rolling sales by week and returning max values of a sum
          swaroop.gantela

          Nicholas,

           

          I do think that this can still be done with Table Calculations (running sum) and with Week on the Detail Shelf.

          While the week on the detail shelf does cause multiple copies of the needed value,

          you can put a calculation of INDEX() on the Filter Shelf, set the compute usings accordingly,

          and just filter it down to one.

           

          In the workbook v10.5 attached in the Forum Thread, I give a demonstration of this technique.

          I likely did not get your percents or totals correct, just wanted to show that graph can

          be achieved using that Index Filter.

           

          This method was particular about the Table Calculation settings (compute using, restarting...)

           

          I also was not clear on how your Warehouse on hand was working because for

          some combinations of StyleName and Week there were two different values.

          I just took the max, but a method could likely be worked out if you needed the sum.

           

          Apologies if I missed it, but would be grateful if you could post a table or a graph

          of what you want your desired values to be.

          • 2. Re: Desperately need help calculating rolling sales by week and returning max values of a sum
            Nicholas Zabilski

            Swaroop, thank you so much for answering me again. This is very close to what I'm looking for. You are correct to question the style name and warehouse discrepancies since there can be multiple style numbers for one style name. An example of what I mean is: Style name would be Sandal, and Style # refers to a specific style/color so sandal black, sandal brown..etc. I should have mentioned that in the original post. Also, some of my style numbers seemed to get combined when I was making sure no sensitive data was included in my example, so the data does look strange, but that was my mistake. Other than that it looks like your solution may work! I'll let you know how it goes once I am able to try applying what you did in my actual project file.

            • 3. Re: Desperately need help calculating rolling sales by week and returning max values of a sum
              swaroop.gantela

              Nicholas,

               

              That makes sense about the name vs. number.

              I think it can be accounted for.

              Wanted to reiterate that the above method was very particular

              about the settings of the Table Calculations. Some fields

              Restart every Style Name, some (like warehouse on hand) restart every week.

              Some are calculated across specific dimensions in a specific order,

              some like warehouse on hand are calculated by cell.