9 Replies Latest reply on Feb 10, 2016 1:48 AM by pavan kumar

# Need Calculated Field, compare current value to previous value

hi, very easy to do in Excel, kind find right combination for the formula within Calculated Field, give errors of "all fields must be aggregate"

I want to compare individual values to their previous value, and categorize accordingly... Please see attached pic... • ###### 1. Re: Need Calculated Field, compare current value to previous value

Hi Jon,

I am illustrating the below example based on Super Store data. (Order Date and Sales)

Create the following Calculations:

1. Difference (Previous vs Current)

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

2. Final Sales:

IF [Difference (Prev VS Current)]=0 THEN LOOKUP(SUM([Sales]),-1) ELSEIF

[Difference (Prev VS Current)]<0 THEN -SUM([Sales]) ELSEIF [Difference (Prev VS Current)]>0  THEN SUM([Sales]) END

Please find the attached workbook.

You may replicate the above logic for your example.

Thanks,

Venu

• ###### 2. Re: Need Calculated Field, compare current value to previous value

thanks V, but I dont think you answered my question.  I am not looking to subtract 2 values.  if you look at the pic it shows what i need.  if i was to try and write it out in layman terms it would look like this.  PlusMinus is name of Calculated field...

PlusMinus

If Current_Value > Previous_Value then  "+"   else

If Current_Value < Previous_Value then  "-"    else

If Current_Value = Previous_Value then  Previous PlusMinus value

END

• ###### 3. Re: Need Calculated Field, compare current value to previous value

Are you looking to do something like this? IF so, the calc is

IF SUM([Sales]) > LOOKUP(SUM([Sales]), -1) THEN '+'

ELSEIF SUM([Sales]) < LOOKUP(SUM([Sales]), -1) THEN '-'

ELSE 'Same'

END

Set to Compute Using Table Down.

Let me know if this is wasn't what you were looking for, or if you need help with it,

Regards,

Rody

• ###### 4. Re: Need Calculated Field, compare current value to previous value

i am trying to create a calculated field that has +, - symbols for each value, the symbol for that value will be based on the previous value.. here it is in Excel • ###### 6. Re: Need Calculated Field, compare current value to previous value

hi Lav

you definetly made it work.  But can you explain the method ? i am trying to figure it out in TBL but cant get exactly what is being done.

Also, i see there are 3 calculated fields.  Do you feel this is necessary?  id like to keep it to 1 calculated field if possible, what do you think?  thanks

• ###### 7. Re: Need Calculated Field, compare current value to previous value

Dear John,

i dont know whether its close or not but this is what i come up with. Hope this will give a kickstart

if IFNULL((SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1),0) = 0

then '+' elseif zn(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) < 0 then '-'

else " "

End • ###### 8. Re: Need Calculated Field, compare current value to previous value

Hi Jon!

I slightly changed my workbook in attach.

I added comments for explanation fields and made two versions - with some fields and with one result field.

How I made it step by step:

1. [Prev# sort] gives 0, if previous value was the same, otherwise it equals [#] field
2. [Prev# to compare] gives the number of row to compare with current value
3. [PrevVal] gives required last non-equal value to compare
4. [+/- (with custom format)] gives needed result based on previous value from [PrevVal]:
• ">" then 1
• "<" then -1,
• "?" then 0, for null or unknown cases
5. And finally for nice view I use custom format to turn 1/-1/0 into "+"/"-"/" ". Format +;-;" " turn positive numbers to "+", negative to "-" and  zero to " "
I recommend a great article about custom number formats Tableau Quick Tip #2 Custom Number Formats - Clearly and Simply

Good luck!