5 Replies Latest reply on Mar 31, 2017 1:59 AM by Prasenjeet Acharjee

    calculation across a row and coloumn

    Prasenjeet Acharjee

      Hi,

       

      I am stuck with a question which my colleague asked my help for. I have a table as shown below.

      In the above table, for each product (for example B2L57B), i have a debit and a credit (-1260 in April and 1260 in June). I want to filter this whole table so that my table has only those data points for which the net sum for debit and credit (-1260 in April and 1260 in June) is 0.

      a second variation of this case is product no. B0T87PA, where sum of (-6000, 22365, 16365) is 0.

       

      So in summary,

      1. i am asked to filter the table to include only those data points for which the net sum is 0 (consider the 2 situations mentioned above).

      2. Sum of only the +ve values for each coloum.

       

      I am attaching the twbx file as well with this post. Any help will be greatly appreciated. Thanks in advance.

        • 1. Re: calculation across a row and coloumn
          John Sobczak

          I can do each one individually as in the attached.

          2 of 2 people found this helpful
          • 2. Re: calculation across a row and coloumn
            Jim Dehner

            Hi

             

            I'm not totally sure I know what you if what you want is a single value that represents the 12 month prior to the selected date or whether you are trying to compare yoy

             

            in any event the attache workbook has a solution for the previous 12 months to a parameter -

            The process was to:

            1. create a new parameter New Date Selector
              1. This parameter has a Data Type DATE
              2. Format Month Year
              3. and the values are a list generated from Order Date
            2. Create a calculated field for 12 month sales
              1.   IF datetrunc('month',[Order Date])> dateadd('month',-12,[new date selector]) and datetrunc('month',[Order Date])<=[new date selector] then zn([Sales])
            3. Create a calculated field for 12 month profit
              1.   IF datetrunc('month',[Order Date])> dateadd('month',-12,[new date selector]) and datetrunc('month',[Order Date])<=[new date selector] then zn([profit])
            4. Create a calculated field for 12 month discount dollars
              1.   IF datetrunc('month',[Order Date])> dateadd('month',-12,[new date selector]) and datetrunc('month',[Order Date])<=[new date selector] then zn([Sales]*zn([Discount])
            5. Create a calculated field for the new discount rate
              1. { FIXED :  (sum([12 month $ discount]))}/{ FIXED :(sum([12 month sales]))
            6. Create a calculated field for the new profit ratio
              1. { FIXED :  (sum([12 month profit]))}/{ FIXED :(sum([12 month sales]))}

             

            It should product the view shown below:

             

            Let me know if this helps

            Jim

             

             

             

            1 of 1 people found this helpful
            • 3. Re: calculation across a row and coloumn
              Prasenjeet Acharjee

              Hi John,

               

              Thanks for your prompt response. Your solution almost solves my 1st part of the problem. Can you suggest a way through which i can get a sum of only the positive values in the table?

              • 4. Re: calculation across a row and coloumn
                Prasenjeet Acharjee

                Hi John,

                 

                It will be of great help to me if you can find some time to explain to me how you arrived at the individual solutions.

                • 5. Re: calculation across a row and coloumn
                  Prasenjeet Acharjee

                  Hi Jim,

                   

                  Thanks for your response and effort that you put in to solve my problem. What i was looking for is more inline with John's answer.

                   

                  Warm regards,

                  Prasenjeet