6 Replies Latest reply on Oct 5, 2018 12:30 AM by Mahfooj Khan

    Difference Between Columns

    mursid rahman

      Hi,

       

      I would like to find the difference in value between two different columns.

       

      1. Sale of Appliance -  Sales of Accessories.

      2. If either columns has NULL value then it will take the existing value. For example Aaron Hawkins value should be 35 and Adam Bellavance should be 160 - 196

       

      Zhouyi Zhang

       

      Thank you.

        • 1. Re: Difference Between Columns
          Mahfooj Khan

          Hi,

           

          Try this,

          WINDOW_MAX(LOOKUP(ZN(SUM([Sales])), -1) - ZN(SUM([Sales])))

          Let us know if this help.

           

          Mahfooj

          • 2. Re: Difference Between Columns
            mursid rahman

            Almost! And thank you for your solution. I forgot to mention in details.

             

            What if there are other columns but I just want the sum between Appliance and Accessories and divide by 2?

             

            So far what I got is that when Appliances and Accessories have values there's no problem. The two values will be added and divided by 2. As an example for Adam Bellavance

             

            But the problem is when there is only one value in either Appliance or Accessories. The value will also divided by 2. What I want is that it's original value. As an example for Aaron Hawkins where it should be 34.77 and not 17.385

             

            My calculation is

             

            IF ATTR([Sub-Category]) == "Accessories" AND ISNULL(SUM([Sales])) OR

            ATTR([Sub-Category]) == "Appliances" AND ISNULL(SUM([Sales]))

            THEN

            WINDOW_MAX(LOOKUP(ZN(SUM([Sales])), -3) + (LOOKUP(ZN(SUM([Sales])), -2)))

            ELSE

            WINDOW_MAX((LOOKUP(ZN(SUM([Sales])), -3) + (LOOKUP(ZN(SUM([Sales])), -2))))/2

            END

            • 3. Re: Difference Between Columns
              Mahfooj Khan

              Hi,

               

              I guess using LOD will be much more easier in this case,

               

              Create few LOD{} caclulations using below logic to get the output.

               

              [Accessories - LOD]

              {FIXED [Customer Name]:

              SUM({FIXED [Customer Name]:SUM(IIF([Sub-Category]='Accessories',[Sales],NULL))})

              }

               

              [Appliances - LOD]

              {FIXED [Customer Name]:

              SUM({FIXED [Customer Name]:SUM(IIF([Sub-Category]='Appliances',[Sales],NULL))})

              }

               

              Variance - LOD

              ZN([Accessories - LOD])-ZN([Appliances - LOD])

               

              Final calculation

              SUM - LOD:

              IF ISNULL([Accessories - LOD]) AND ISNULL([Appliances - LOD]) THEN 0

              ELSEIF ISNULL([Accessories - LOD]) AND NOT ISNULL([Appliances - LOD]) THEN ZN([Appliances - LOD])+ZN([Accessories - LOD])

              ELSEIF NOT ISNULL([Accessories - LOD]) AND ISNULL([Appliances - LOD]) THEN ZN([Appliances - LOD])+ZN([Accessories - LOD])

              ELSEIF NOT ISNULL([Accessories - LOD]) AND NOT ISNULL([Appliances - LOD]) THEN ([Accessories - LOD]+[Appliances - LOD])/2

              END

               

              Here is the output.

              Hope this help.

               

              Mahfooj

              • 4. Re: Difference Between Columns
                Mahfooj Khan

                I've simplified the SUM-LOD calculation 

                 

                IF NOT ISNULL([Accessories - LOD]) AND NOT ISNULL([Appliances - LOD]) THEN ([Accessories - LOD]+[Appliances - LOD])/2

                ELSE ZN([Appliances - LOD])+ZN([Accessories - LOD])

                END

                 

                Hope this will help.

                 

                Mahfooj

                • 5. Re: Difference Between Columns
                  mursid rahman

                  Hello Mahfooj,

                   

                  Your solution works superbly. Thank you so much for the assist!

                   

                  Regards,

                  Mursid.

                  • 6. Re: Difference Between Columns
                    Mahfooj Khan

                    You're welcome I'm glad I could help!