topic Re: Calculate difference between values from three columns in Dashboards & Analytics Discussions

Calculate difference between values from three columns

Ruchita — Wed, 31 May 2023 04:59:14 GMT

Hi,

I need to calculate the difference amongst values present in three columns.

It needs to look something like this:

A	B	C	Difference
10	10	10	0
10	10	9	1
10	0	0	10
10	9	10	1

Can someone guide me to formula or function that can help me acheive this result ?

Re: Calculate difference between values from three columns

Random_1191 — Wed, 31 May 2023 09:11:24 GMT

what are the conditions of "differences" ? eg 10 = 10 = 10 is 0 , but why in row 3 when you got 10 0 0 the difference is 10 instead of 20 ? maybe You can provide more examples.

Re: Calculate difference between values from three columns

Ruchita — Wed, 31 May 2023 11:05:57 GMT

the calculation goes like this:

row 1 : 10 - 10 - 10 (all the three are same) = 0

row 3 : 10 - 0 - 0 (10 is the remaining value) = 10

row 4: 10 - 9 (since two values are same i.e 10, it needs to take the difference from the value that is different)

Re: Calculate difference between values from three columns

RADSr — Wed, 31 May 2023 14:18:07 GMT

You want to subtract distinct values where the distinct values are ordered desc ?

Offhand I'm thinking "Incorta over Incorta" ( i.e. use a result set as your insight data source ) where you get your three values and order them accordingly.

Re: Calculate difference between values from three columns

Ruchita — Thu, 01 Jun 2023 04:59:19 GMT

Hi RADSr,

Thank you for your response.

The order need not be asending or descending in this case, because we're trying to determine if the values match in all three columns, if it does not, it needs to show by how much are the values different.

Re: Calculate difference between values from three columns

Prince — Thu, 01 Jun 2023 06:22:55 GMT

If I understood the case correctly, you need the following:

max(abs(A-B), abs(B-C), abs(A-C))

the maximum of the absolute difference between any 2 columns, right?

Re: Calculate difference between values from three columns

RADSr — Thu, 01 Jun 2023 13:58:04 GMT

Values 1, 9, 8 What result do you expect?

You've mentioned that ties are ignored, but what exactly is the algorithm you are trying to employ?

Re: Calculate difference between values from three columns

Ruchita — Fri, 02 Jun 2023 05:12:30 GMT

Hi Prince,

Thank you for your understanding of the case aptly.

I used a nested max() because individual max() gives an error that it accepts only 2 parameters, something like this:

max( abs(A-B),

max(

abs(B-C), abs(A-C)

)

Appreciate your prompt response and support !