Dynamic color format values - Tableau

Question

I want to color format the values (.csv file format) based on the below conditions in tableau desktop.

If consecutive increase of the values or same values with increase values from start Date then values should be Red color .

If Consecutive decrease of the values from startDate then values color should be Green Color

If the values are Increase and then decrease from start date then Yellow.

If the values are decrease then increase then yellow.

For the above data the expected color for the student values should be below..

Student:Mon,Pat,Henry,Kim Yellow,

Jack,stanley Red

Kevin,Lendl -Green

I have tried below code but not working as expected..

Please correct if i missing any thing thing.

//Green - consecutive downward trend
if
window_sum(if FIRST() = 0 then 0
elseif sum([Value]) < lookup(sum([Value]),-1) then 1
end) = MAX([Number of Days]) then "Green"

//Yellow - downward and upward trend but not consecutive  

elseif window_sum(if FIRST() = 0 then 0  
elseif sum([Value]) < lookup(sum([Value]),-1) then 1  
end) > 0 and    
window_sum(if FIRST() = 0 then 0  
elseif sum([Value]) < lookup(sum([Value]),-1) then 1  
end) < MAX([Number of Days]) then "Yellow"  

//Red - consecutive upward trend  

elseif  
window_sum(if FIRST() = 0 then 0  
elseif sum([Value]) > lookup(sum([Value]),-1) then 1  
end) = MAX([Number of Days]) then "Red"  
end

Thanks for your help in advance !!

Andy TAR Sols Andy TAR Sols · Accepted Answer · 2020-04-27T12:24:16

Without knowing your data structure you can look into table calculations. This may not be straightforward, especially to get the "Compute Using" settings right, but some logic as follows may work.

Calculate the day on day differences (a table calculation will do that).

If the day on day diff is positive then give it a value 1, is negative set it to -1. This would be an IF statement around your table calculation.

Next SUM the result.

Calculate how many dates there are, which will provide the maximum possible number if all are increasing or decreasing every day.

Finally compare the result of the summing if up or down with the maximum possible. This field can sit on the colour shelf.

I haven't tested any of this but, from a logic perspective, you may be able to make this work.

Edit based on comments:

Build the following calculated fields, it should work. You'll be able to troubleshoot calc by calc if having "compute using" challenges. Put the [Colour] field on the colour shelf:

[DailyDiff]: ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -1)

[isUpOrDown]: IF [DailyDiff] >0 THEN 1 ELSEIF [DailyDiff] <0 THEN -1 ELSE 0 END

[TotalUpOrDown]: WINDOW_SUM([isUpOrDown])

[NumDays]: {COUNTD([Date])}-1 //1 less to account for the first day

[Colour]: IF [TotalUpOrDown] = MIN([NumDays]) THEN "Increasing" ELSEIF [TotalUpOrDown]=-MIN([NumDays]) THEN "Decreasing" ELSE "Other" END

Dynamic color format values - Tableau

1 Answers