4 Replies Latest reply on May 23, 2013 1:10 AM by anna.lobanova Branched to a new discussion.

# Adding week over week % change by week and category

Hello,

I am struggling with creating a calculation that would add a % change per week per category for a Table like example below:

 week Category Pageviews 10 Video 1200 10 Audio 566 11 Video 1000 11 Audio 300 12 Video 1110 12 Audio 250 13 Video 890 13 Audio 307 14 Video 980 14 Audio 285

How can I calculate % change for Audio wk 10 with Audio wk11 instead of Audio wk 11 with Video wk 11?

 week Category Pageviews % change 10 Video 1200 20.0% 10 Audio 566 88.7% 11 Video 1000 -9.9% 11 Audio 300 20.0% 12 Video 1110 24.7% 12 Audio 250 -18.6% 13 Video 890 -9.2% 13 Audio 307 7.7% 14 Video 980 14 Audio 285

At the moment I have this:

(ZN(SUM([Pageviews])) - LOOKUP(ZN(SUM([Pageviews])), 1)) / ABS(LOOKUP(ZN(SUM([Pageviews])), 1))

but the result is the % change from previous line not same line in the week before.

If I change the order of dimensions from WEEK - CATEGORY to CATEGORY - WEEK - the % change is calculated correctly (that is it is based on the same line in the previous pane=category) but this is not what I want.

Thank you for any tips!

Anna

• ###### 1. Re: Adding week over week % change by week and category

Anna,

This is one of the beautiful uses of "Compute Using."  I have attached a mock-up using your data.  Right-click on the table calculation and select "Edit Table Calculation" to see what I did.  It was just changing one box.

Hope this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 2. Re: Adding week over week % change by week and category

Hello Brad, thanks a lot, your solution is working I slightly modified it - to show the most recent week on top - by changing "Display the value as a percent difference from" = "Next"

I have one more question - how can I do the % change calculation by creating a new Calculation Field instead of adding Table Calculation on an existing measure?

If I copy the calculation

(ZN(SUM([Pageviews])) - LOOKUP(ZN(SUM([Pageviews])), 1)) / ABS(LOOKUP(ZN(SUM([Pageviews])), 1))

= [TEST: % change] it does not work with the modified structure (=the most recent week on top). And it shows % change for the next line:

Thank you,

Anna

• ###### 3. Re: Adding week over week % change by week and category

Anna,

This is where "Compute Using" comes in.  Take a look at the following screenshot.

Hope this helps,

Associate Consultant

Mariner, LLC