I've done this before, but a lot of the calc's depend on the shape/format of your data.
Can you please provide some sample data with enough records to show how it should work in all situations ? That should make it simple for me or someone else to build this for you.
What's not working for you? I can see some tweaks to the formulas wouldn't hurt but what you have may work anyway?
if datediff('day', [Datetimesearch], today())>=0 and datediff('day', [Datetimesearch], today())<=7 then [measure] end
if datediff('day', [Datetimesearch], today())>=8 and datediff('day', [Datetimesearch], today())<=14 then [measure] end
Variance to get % change:
SUM(Calc 1 from above) / SUM(calc 2 from above)-1
Formula to create shape based on above:
IF [variance] > 0 THEN 'Up' ELSEIF [variance] = 0 THEN 'Unchanged' ELSE 'Down' END
The shape i am using is always fixed as the result of the formula is only ever one state, as in positive, negative or equal.
I have a workaround using ALT Codes instead of the shapes formula.
Strange you would need to do that. Is the Variance calculation returning the expected results?
Yes, the variance calculation is returning the expected results but the issue is that when i initially set the symbol based on the result, it is correct.
However, when the date moves on and the Last 7 and Previous 7 calculations update, the may have different totals and as such, the variance could be the opposite of its original, as in if it was a positive variance initially, it may have become negative and therefore the symbol should be different but as it was set initially, i dont think tableau knows to change it.
ok, so if you create a simple table you see something like the below?
Dimension Variance ShapeFormula X -0.5 Down Y 0 Unchanged Z 0.1 Up
Thanks, ill take a look at that on Monday and get back to you.
The use case i have is to show the last 7 days and previous 7 days as big numbers with the symbol next to them.