I am illustrating the below example based on Super Store data. (Order Date and Sales)
Create the following Calculations:
1. Difference (Previous vs Current)
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 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...
If Current_Value > Previous_Value then "+" else
If Current_Value < Previous_Value then "-" else
If Current_Value = Previous_Value then Previous PlusMinus 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 '-'
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,
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
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:
- [Prev# sort] gives 0, if previous value was the same, otherwise it equals [#] field
- [Prev# to compare] gives the number of row to compare with current value
please read about table calculations here Table Calculation Functions
- [PrevVal] gives required last non-equal value to compare
- [+/- (with custom format)] gives needed result based on previous value from [PrevVal]:
- ">" then 1
- "<" then -1,
- "?" then 0, for null or unknown cases
- 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
PlusMinusField.twbx 30.0 KB