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

# Difference Between Columns

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

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

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

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

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

Hello Mahfooj,

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

Regards,

Mursid.

• ###### 6. Re: Difference Between Columns

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