8 Replies Latest reply on Apr 21, 2018 12:58 AM by Dan Dan

Last 7 Days, Previous 7 Days with variance and symbol

Hi,

I am trying to work out how to set up tableau to show me the following on a rolling basis;

• Last 7 days sum or count of measure
• The 7 days previous to this
• The variance between the Last 7 and the previous 7
• A Symbol which changes dynamically based on the variance between the last 7 and the previous 7 so upward triangle if Last 7 higher than Previous, downward if Last 7 lower than Previous 7 or sideways if same.

I have tried using the following calcs but the symbol based on the difference needs to be updated as its only ever positive or negative or equal;

Last 7:

if datediff('day', [Datetimesearch], today())>=0 and datediff('day', [Datetimesearch], today())<=7 then (sum of measure) end

Prev 7:

if datediff('day', [Datetimesearch], today())>=8 and datediff('day', [Datetimesearch], today())<=14 then (sum of measure) end

Variance to get % change:

(Calc 1 from above / 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

Any help is greatly appreciated,

Thanks,

Daniel

• 1. Re: Last 7 Days, Previous 7 Days with variance and symbol

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.

• 2. Re: Last 7 Days, Previous 7 Days with variance and symbol

Hi Chris,

Thanks for coming back to me.

The data we have is in columns so we have a date in column a and then the value in column b, as below.

Does this data structure work for you?

Thanks,

Daniel • 3. Re: Last 7 Days, Previous 7 Days with variance and symbol

What's not working for you? I can see some tweaks to the formulas wouldn't hurt but what you have may work anyway?

Last 7:

if datediff('day', [Datetimesearch], today())>=0 and datediff('day', [Datetimesearch], today())<=7 then [measure] end

Prev 7:

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

• 4. Re: Last 7 Days, Previous 7 Days with variance and symbol

Hi Andrew,

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.

thanks,

Daniel

• 5. Re: Last 7 Days, Previous 7 Days with variance and symbol

Strange you would need to do that. Is the Variance calculation returning the expected results?

• 6. Re: Last 7 Days, Previous 7 Days with variance and symbol

hi Andrew,

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.

Thanks,

Daniel

• 7. Re: Last 7 Days, Previous 7 Days with variance and symbol

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
• 8. Re: Last 7 Days, Previous 7 Days with variance and symbol

hi Andrew,

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.

Thanks,

Daniel