10 Replies Latest reply on Jan 14, 2019 8:39 PM by Shinichiro Murakami

# YoY same week % change

Hi, I'm trying to calculate the % change of % of total sale YoY for same week however I'm not able to do the correct calculation.

My raw data is as below for years 2017 and 2018 where total_sales = b_sales + c_sales + etc:

And I'm trying to get the data in the format like below that represents the YoY change for the respective week number for 'Brand b' in Shop A:

What I've done:

Previous Year's sales %-> {exclude [year] : sum(if [year]={min([year])} then ([b_sales]/[total_sales]) end)}

Latest Year's sales %-> {exclude [year] :sum(if [year]={max([year])} then ([b_sales]/[total_sales]) end)}

and then

% change = [latest_year] - [prev_year] (because the above values are already a %. So I want the difference of only the % values above.

• ###### 1. Re: YoY same week % change

Hope this helps.

Thanks,

Shin

• ###### 2. Re: YoY same week % change

Thanks. I tried this out and agree works fine. However, I need a view where the 'year' does not appear like below:

The problem is that with the suggestion when I remove the year, the view throws and error:

• ###### 3. Re: YoY same week % change

Two more steps.

You cannot remove the "year" , just hide and don't show.

If this helps, please mark my answer as correct /helpd from original post, not from inbox.

Thanks,

Shin

• ###### 4. Re: YoY same week % change

Thanks, this works.

• ###### 6. Re: YoY same week % change

You are welcome.

Shin

• ###### 7. Re: YoY same week % change

As a follow up to this, I have a question.

I have the following %share for the given years and weeks:

Because I wanted to tabulate the difference in %shares, I'm getting the below result (basically [%share given (year,week) - (year,week-1)].

Example, weeknum1 for 2017 and 2018 -> 13.4 - 11.3 = 2.1% (change)

I'm using the formula - > ZN([%share]) - LOOKUP(ZN([%share]), -1) calculated on year level.

The problem is because there is no data for weeknum 3 and 4 (and likewise for rest of 2019), its doing the calculation 0-11.8% = -11.8% and 0- 10.5% = -10.5% which is not desired. Is there a way to show it as blank?

• ###### 8. Re: YoY same week % change

[%share] -( LOOKUP([%share]), -1)

1 of 1 people found this helpful
• ###### 9. Re: YoY same week % change

Thanks, this works

• ###### 10. Re: YoY same week % change

You are welcome.

Made typo, but anyways, remove ZN.

Shin