9 Replies Latest reply on Oct 11, 2018 9:51 AM by Joe Oppelt

    Calculation previous 12 week's and 4 week's sales average dynamically over two years of time

    Gowri Shankar Rajavelu

      Hi all,

       

      I am migrating my weekly business review report from excel to tableau. My report has last 12 week's average and 4 week's average. I need to dynamically use last years week data for example if I am viewing Week 3's report then tableau should calculate last 4 week's average by using week 52 from 2017 and for 12 week's average use the first three weeks from 2018 and the last 9 week's from 2017. Any help on how to approach and make this work will be appreciated. My data contains date. I calculate week and year using a calculated field.

       

      Thanks in advance,

      Gowri Shankar

        • 1. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
          Joe Oppelt

          You don't need the calc fields.  Put your date field on a sheet broken down by weeks.  (Tableau has good date functionality and will do that for you.)

           

           

          Then you do a window_avg(Sum([whatever]), -3, 0)

           

           

          For each week, Tableau will look back three weeks plus the current week, and average that.  (Ditto -11 to get the 12 week average.)  The data needs to be in the table for the sheet though.  You can't just filter for one week you want to display.

           

           

          First get that part working, and then we can talk about displaying selected weeks from the table.

           

           

          If you can't get it to work, upload a sample workbook that simulates what you have and I'll work with you to get it the way you need.

          1 of 1 people found this helpful
          • 2. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
            Gowri Shankar Rajavelu

            I have attached a sample workbook where it shows how I achieved week filters and I also used the date field to get week numbers as you suggested. What I want to achieve is to use that week number as a parameter for example when someone wants to see the report for week 25 then when they click week 25 it should automatically take the data from week 1 this year for sales and for other calculations like Week on Week and year on year calculations should take necessary data. I used two parameters as week start and week end to achieve this where I keep week 1 as the week start.

            • 3. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
              Joe Oppelt

              I played with your data a bit.  We can probably use your year and weeknum fields, but one thing has to change. 

               

              Here is a screen shot of your data when I use Tableau's date stuff:

               

              December 31 is an oddity.  I am trying to create an index value that increments by week.  (Small circle in the screen shot.)  You can see that the week value and the index value is 52 on December 30.  Tableau is assigning an extra week here to handle the 365th day of the year.  But your business considers it week 1 of 2018.  See Screen shot using your fields:

               

              I want December 31 to be index value 53 (or whatever week your business is considering it relative to January 1.  For 2017/2018 you're seeing it as week 1 of 2018.)  I don't like what Tableau is doing with its own internal handling of that day, so if we could get 12/31/17 to be included in Year=2018 in your data, I can make the table calcs walk along the index field I made, and we can go backward seamlessly.

              • 4. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
                Gowri Shankar Rajavelu

                It is a mistake on my part. I missed changing 12/31/17 to 2018. For our business we do consider 12/31/2018 as year 2018 and Week 1 of 2018. So we can work with your index field and create table calcs.

                • 5. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
                  Joe Oppelt

                  In the attached I made Sales Summary 2.  First I made a calc [index].  Whenever I am messing with table calcs I usually make this calc.  I put it on my sheet for testing purposes, and when I can get the value of INDEX to behave the way I want, I know how I want to address my other table calcs.

                   

                  My goal here is to get an incrementing value on INDEX from weeknum to weeknum -- including a proper span into the next calendar year.  (When your data changes 12/31/2017 to year=2018, the calc will handle that correctly.)

                   

                  I'm not sure how you will determine the week to analyze, so I made a parameter to pick a date, and whatever week it is, that's the week we're starting from.  So I made [Analysis Date].  Then I used that, and index, to determine the week of the analysis date.  See the calc for that.  I have displayed it in the title of the sheet.

                   

                  Then I created another parameter to let you select between 4 weeks or 12 weeks.  And using that, I created [Get Weeks].  You can play with all this and see that the 1 and 0 values correspond to the proper weeks.  On sheet (3) I dragged [Get Weeks] to filters and selected for value=1.

                  • 6. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
                    Gowri Shankar Rajavelu

                    I changed the year for the date 12/31/2017 to 2018 and the index calculation works as intended. I have attached the type of filters to be used for my report.

                     

                    Can you elaborate on how index() function works or share me a link where I can get accustomed to it.

                     

                    I tried using index as array numbers for example to calculate the 4 weeks average for Week 1,2,3,4 in year 2017 then their index would be 1,2,3,4 respectively. Can I write a formula like (salesnum of selected week + salesnum of selected week - 1 + salesnum of selected week - 2 +salesnum of selected week - 3)/4. The data from previous week would be taken using the index of the week similar how arrays work in programming languages.

                    • 7. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
                      Joe Oppelt

                      INDEX() is a function that tells you which "cell" in the table you are on.

                       

                      In the attached I added a new data source.   (Excel file attached.)

                       

                      I have displayed INDEX on the marks.  (See Sheet 5.)  If you edit table calc for this pill (see screen shot)

                       

                       

                      you will see that it's set to TABLE(across).  This gives me numbers from 1-through-N for each row across.

                       

                      Edit it and change it to TABLE(DOWN).  Now you get 1-through-N down the sheet.  Change it to "down then across" or "across then down".  You get 1-through-N for the whole table, either counting down first, or counting across first, depending on what you pick.  Pick "Cell".  You get 1 for every cell.  There is only one mark per cell.  (If there were a 3rd dimension, and if I dragged that dimension to the data shelf (not rows or columns) we would be getting x-many marks per cell depending on the number of rows within that 3rd dimension, and the index would be 1-through-N within each cell.)

                       

                      So index() is a tableau internal number.  tableau uses its index values to walk backward and forward when you use a table calc to do a window_sum over a range of values, for example.

                       

                      On your filters to be used sheet, you would either have an index value of 1 on each row if you set it to table across, or 1-through-6 if you set it to table down.  your entire table consists of 6 values.  When you use standard filters, you remove actual rows from the table.  Only those rows that satisfy the filter(s) exist in the table.

                       

                      On my Sales Summary (3) sheet I used a table calc as a filter.  Not a standard filter.  When you use a table calc as a filter, it leaves the entire table intact, and just governs which part of the table gets displayed.  At it stands when I uploaded the latest version here, all 35 index values still exist in the table, but only 12 of them are getting displayed.  You can see that the first index value displayed is 19.  The preceding 18 are still in the table.

                       

                      On Sales Summary (2) I have used your weeknum=23 setting, and I have displayed index on the mark.

                       

                      Here's where table calc settings start getting tricky.  I have used the same settings for index as I have on sheet (3).  The settings I have chosen say to cycle through the three selected dimensions ([year], [date], [weeknum]), and when weeknum changes, increment index.  I have made sheet(4) to simulate what that looks like to Tableau.  All the date values for a given weeknum/year combination are shoved into a single cell.  And ignore [Type].  (So in some respects, this is like a table(across) for each [Type], but only after cycling through the years and weeknums.)

                       

                      On sales Summary 2, we have only two index values -- one for each year.  We don't have 35 cells any more.  The filter has deleted from the table all rows except weeknum 23.  (One cell for each year.)  So the index function dutifully counts from 1 to 2 on each [Type], and that's it.

                       

                      My filter on sheet (3) assumes that all rows are still in the table.  I figured out which index value encompasses the [Analysis Date] in [Week of Analysis Date], and then I grab the correct other index values from there.

                      1 of 1 people found this helpful
                      • 8. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
                        Gowri Shankar Rajavelu

                        Hi Joe,

                         

                        Thank you for taking your time for helping me out. But approaching deadlines, heavy workload and my inability to share my data with you is hindering my learning. I couldnt apply your solution as of now. One more question is there a way to select the data which is not selected in the filter? For example if I have selected the year as 2018 i need the display the data of 2017. My calculated field is similar to the following

                         

                        if [Yearnum] = [Parameters].[Year] - 1 then [sales] end

                         

                        As you can see this condition will never be TRUE and will always be empty. But I hope you can get what I am trying to achieve.

                         

                        Regards

                        Gowri Shankar

                        • 9. Re: Calculation previous 12 week's and 4 week's sales average dynamically over two years of time
                          Joe Oppelt

                          If you filter using a standard quick filter, any rows that do not satisfy the filter criteria are deleted from the underlying table.  So in the last example workbook I uploaded, the sheet, Sales Summary(2) uses a quick filter for weeknum=23.  The only rows in the table at that point will be for weeknum 23 (regardless of the year.)


                          But in Sales Summary (3) I used a table calc filter ([Get Weeks]).  A table calc filter leaves the entire table intact, but just displays the rows from the table that satisfy the filter criteria.  So on that sheet I'm showing rows where [index] is >= 71 and upwards.  Index values 1-through-70 are still in that table.

                           

                          If you made a table calc that gets used as a filter, the data for 2017 will still be in the table.

                           

                          LOOKUP(attr([Year]),0)

                           

                          This calc says "give me all current rows (that's what the zero does) where the Year = what I select."  Put that calc on filters, and you'll get a filter from it that will give you choices of all the years in your data.  Select 2018, and that's all that will be displayed.  But all the 2017 rows will still be in the table for you to access.