3 Replies Latest reply on Nov 22, 2013 1:18 PM by Jessica Lee

    Calculating weekly & monthly % difference

    Jessica Lee

      Hi everyone,

       

      I need some help with calculating percentage difference.

      Attached is the data set I have.

      Using the table calculation, I could compute % of daily changes in # of product.

      What I'd like to see now is the % of weekly changes, and % of monthly changes.

      The final table that I am expecting to see is this:

       

      DateDailyWeeklyMonthly
      10/18/13629.51%
      10/19/13-99.33%
      10/20/13-33.33%
      10/21/13150%
      10/22/1320%
      10/23/13-50%
      10/24/13966.67%% difference from last week (10/10-10/17)'s total
      10/25/13-87.50%
      10/26/13-25%
      10/27/1315000%
      10/28/13-99.12%
      10/29/130%
      10/30/13-25%
      10/31/132433.33%% difference from last week (10/18-10/24)'s total% difference from last month's total

       

      Is there any way that I can display all three columns in one sheet?

      Or should I create this in 3 different sheets and consolidate them in to the dashboard? This is the only other possible option that I can think of.

        • 1. Re: Calculating weekly & monthly % difference
          Tracy Rodgers

          Hi Jessica,

           

          This can be done. First, you'll need to place Week(Load Date) on the rows shelf as well.

           

          Then, place Product on the view. Right click and select Quick Table Calculation...>Percent Difference. Right click again and select Edit Table Calculation. Select Advanced...>Place Year, Month then Day of Load Date in the addressing box. Click OK. Choose Deepest for at the level and Restarting Every: None. This is for the Daily Percent Difference.

           

          For Weekly, place another instance of Product on the view. Follow the same instructions as above, only place Week, then Day, Month and Year of Load Date into the Addressing box. Choose Week of Load Date for At the level and none for restarting every.

           

          Finally, for Monthly, follow the same instructions. Choose Load Date for the Compute Using, Month of Load Date for at the level and None for restarting every.

           

          From here, if you'd like to rename them all, edit each calculation. Then, click Edit Formula... in the dialog box--a calculated field will appear. Simply rename the calculation and click OK, OK.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Re: Calculating weekly & monthly % difference
            Matt Lutton

            I attempted to set this up based on Tracy's instructions.  You will say NULL values where there is no difference to compute.  Otherwise, the instructions were pretty much spot on.

            • 3. Re: Re: Calculating weekly & monthly % difference
              Jessica Lee

              Great, this works perfect.

              Thank you so much for the help!