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

    anna.lobanova

      Hello,

       

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

       

      weekCategoryPageviews
      10Video1200
      10Audio566
      11Video1000
      11Audio300
      12Video1110
      12Audio250
      13Video890
      13Audio307
      14Video980
      14Audio285

       

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

       

      weekCategoryPageviews% change
      10Video120020.0%
      10Audio56688.7%
      11Video1000-9.9%
      11Audio30020.0%
      12Video111024.7%
      12Audio250-18.6%
      13Video890-9.2%
      13Audio3077.7%
      14Video980
      14Audio285

       

      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