3 Replies Latest reply on May 24, 2017 4:53 PM by Shinichiro Murakami

    Calculate Year over Year Percentage Sales Increase Based on Integer Week & Integer Year (Not date Format)

    Bjarni Magnusson

      I have a data source that is structured per below

           

      Item NumberLocation NumberYearWeekSales QTY
      xy162050
      xy172060
      xy162133
      xy172127

       

       

      I have created a workbook that shows a line graph with week numbers across columns and sales quantity in the rows. I have pulled in the year into the marks section (colored) so that each weeks sales can visually be compared to previous years values. I have attached the workbook.

       

       

       

      My problem is that I cannot see any good way to calculate percentage change year over year and display on the graph on secondary axis as percentage. Ideally I would like this to be flexible (not a table calculation) so that If i pull in additional items or break this out by location it does not mess up the logic.

       

      My ideal solution would be something similar to a SUMIFS function in excel- that would essentially insert a new column in the data source that summed up the previous years sales for that specific week. So for example- if i am in Year 17, Week 21- an adjacent column would give me the sales quantity for Year 16, Week 21. That would make it easy enough to put that value into a calculated field to compute the percentage change and display in whatever way I wanted in final view.

       

      Let me know if you need any more specifics. I have attached the workbook.