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)

I have a data source that is structured per below

 Item Number Location Number Year Week Sales QTY x y 16 20 50 x y 17 20 60 x y 16 21 33 x y 17 21 27

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.

• ###### 1. Re: Calculate Year over Year Percentage Sales Increase Based on Integer Week & Integer Year (Not date Format)

Hi Bjarni

In you case, I think combining LOD and IF Statement plus context filter will help.

When you add other filters, all the filter needs to be "Context" to make LOD calc work after filtering.

Thanks,

Shin

• ###### 2. Re: Calculate Year over Year Percentage Sales Increase Based on Integer Week & Integer Year (Not date Format)

Thank you. That solution works for the specific instance I provided. I was hoping to be able to use it in a few other scenarios in which the calculation breaks down however. Specifically i would like to also show this calculation for 2016 vs. 2015 like we were able to do successfully for 2017 vs. 2016. Is it possible to enable that?  When i drag the % change calculation to a separate column and separate out by year it appears to duplicated the % for both 16 & 17:

Additionally I would like for the calculation to not be impacted by selecting 2015 an additional data point in the filter

• ###### 3. Re: Calculate Year over Year Percentage Sales Increase Based on Integer Week & Integer Year (Not date Format)

Bjarni,

With my current approach, you can not get the delta of "2015 vs 2016" and "2016 vs 2017" at the same time.

If you are talking about the issue of only doubled Bar graph, to fix issue is just change the type of stack.

Bar chart's default setting is always "Stack ON" and Line chart's default setting is always "Stack OFF".

2015 and 2016's data is 100% over-wrapped and drag and drop to change the sort order on color shelf will bring different color.

Let me know you are asking different question.

Thanks,

Shin