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

    Last 7 Days, Previous 7 Days with variance and symbol

    Dan Dan

      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