5 Replies Latest reply on Dec 19, 2016 2:40 AM by Shaun Brookes

    Excluding a value from a calculation

    Shaun Brookes

      Hi,

       

      I'm hoping someone can assist me with this, in the screenshot below, the table is showing number of records, Month is the variable on the left and Offset (0,1,2) in the columns, what im trying to do is to calculate the total of each column, divided by the total of the first column (offset 0) excluding the months that fall after the last month in each column. So for example, if we look at the column showing offset 1, I want to calculate the total of that column (384,684) divided by the total of column 0 excluding Decembers 2016 value (the green highlighted area). and for column 2, i want to calculate the total of column 2 divided by the total of column 0 excluding December and November (the orange highlighted area) and so on and so on for all other columns.

       

      Can anyone assist?

       

      Thanks

       

      Shaun

        • 1. Re: Excluding a value from a calculation
          Walt Reed

          Hey Shaun,

          I'm trying to take a stab at this. Can you explain how the columns 0,1,2 are calculated? You mention that it's an offset, but how?

           

          Walt

          • 2. Re: Excluding a value from a calculation
            Shaun Brookes

            Hi Walt,

             

            Thanks for having a look. The offset is calculated by the months difference between 2 dates (the one you see in the screenshot and another date in my dataset)

            • 3. Re: Excluding a value from a calculation
              Walt Reed

              Hey Shaun,

              This one is pretty tricky, I'm not having much success so far. Do you mind posting a packaged workbook so myself and other folks can take a look?

               

              I was thinking this might be easier to do with custom SQL....not sure what your comfort level is with writing those types of queries.

               

              Walt

              • 4. Re: Excluding a value from a calculation
                Jonathan Drummey

                How's this?

                 

                Screen Shot 2016-12-16 at 2.34.49 PM.png

                 

                This is done using a healthy dose of table calculations and custom grand totals, if you want (a lot of) background reading check out the links at http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations and http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1 and http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2.

                 

                For the data I used Superstore and used a calc to change the Categories into the Offset dimension, and filtered out some data to make it look like the screenshot above.

                 

                Then here's roughly how I built out the view and the calcs. I created two different solutions, one with a single calc and one with two calcs and this latter one is the one in the screenshot above where we can get different number formats for the detail rows and the grand total, so I'll cover that one:

                 

                1) Created custom dates for the month & year dimensions and used those on Rows.

                2) Created duplicates of the two custom date dimensions and put the copy dimensions onto the Level of Detail Shelf. This is using the duplicated dimension technique from the 2nd grand totals post. This is necessary to give us enough detail inside the grand total to get the calcs to work right.

                3) Created a First Offset Sales table calculation with the formula LOOKUP(SUM([Sales]),FIRST()). With a Compute Using on Offset it returns the values of the Offset 0's sales to each new offset. Since it only does the carry forward for the marks in the view it *does not* carry forward Offset 0/Dec 2016 sales into Offset 1/Dec 2016.

                4) Created a Sales for Dual table calculation with the formula IF SIZE() = 1 THEN SUM([Sales]) END that has a compute using on Year (copy) and Month (copy). With that compute using setting SIZE() only returns 1 in the detail rows, not the grand total. This has the standard number format.

                5) Created a Ratio for Dual table calculation with the formula

                IF SIZE() > 1 AND FIRST() = 0 THEN

                    WINDOW_SUM(SUM([Sales])) / WINDOW_SUM([First Offset Sales])

                END

                This also has a compute using on Year (copy) and Month (copy). With this compute using setting SIZE() will be >1 in the grand total, and the FIRST()=0 is used to return only a single result out of all the months that are in the grand total. The two WINDOW_SUM's add up the numbers to create the ratio.

                 

                The workout worksheet shows all the calcs, then I duplicated that sheet and moved pills to create the final view, the one last step was to turn off Analysis->Stack Marks to get rid of the ellipses in the Grand Total and restore the regular layout.

                 

                v10.0 workbook is attached, let me know if you have any questions!

                 

                Jonathan

                2 of 2 people found this helpful
                • 5. Re: Excluding a value from a calculation
                  Shaun Brookes

                  Thanks so much. works perfectly!