6 Replies Latest reply on Sep 12, 2018 8:59 PM by Josh Reynolds

    Stacked Bar Chart with Running_Avg - replace null values with previous values

    Josh Reynolds

      Hey All,

      I'm sure this is a fairly simple one but I'm stuck...

       

      I've made a Stacked Bar Chart visualizing 3 categories of Sales and the values are calculating using Running_Avg too smooth the data and show the trend.

       

      Issue I'm facing is that some of the weekly values are null.

       

      My goal was to replace the null values with the previous weeks value.

       

      For example below, Week 5 is blank but would like the bar to be exactly the same as Week 4.

       

       

      The data is pivoted and current formula I'm using is:

       

       

      Text Version:

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      if attr([Production Measures])="Sales 1" and (SUM([Meaure Value])>0) then RUNNING_AVG(SUM([Meaure Value]))

       

      elseif attr([Production Measures])="Sales 2" and (SUM([Meaure Value])>0) then RUNNING_AVG(SUM([Meaure Value]))

       

      elseif attr([Production Measures])="Sales 3" and (SUM([Meaure Value])>0) then RUNNING_AVG(SUM([Meaure Value]))

       

      ELSE NULL end

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

       

      The Running_Avg was calculating out to the end of the year which is why I added the IF AND combo statement to only pull the +0 values and was playing around with more IF statements using Previous_Value but can't seem to get it working...

       

      Tableau version 10.3. I would post a workbook but i didn't want to post the original one with sensitive info so I was trying to remake it using filtered data but it doesn't seem to be working.. I spent 45 mins trying to recreate but just isn't working! If anyone has a solution based on my current info that would be awesome but if really stuck, then I'll go back and try and get the workbook running.

       

      And most importantly, Thanks !

      Josh.