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



      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.