title: Spotfire: Trying to fill in a 'Null' value with the previous value in the chart from a pivoted cloumn
body: <p>2 Separate data tables for this analysis: </p>
  
  <p>Table A: table is planned output by work week.  ~What we want to happen.</p>
  
  <p>and</p>
  
  <p>Table B: table is actual output by serial number. ~what <em>is</em> happening.</p>
  
  <p>Both tables are being pulled from Microsoft AX, ERP system. (SQL based)
  Current week =49
  current Table B:</p>
  
  <pre><code>Work Week   TestLine2   TestLine3   TestLine4   TestLine5   TestLine6
  40          861         125         104                     1108
  41          1310        325         179         50          2008
  42                      625         454                     2733
  43          1907        674         29                      3493
  44          2507        724         804         74          3792
  45          3107                    1105                    4489
  46          3357        1023        1403                    5014
  47          3732                    1428                    5764
  48          3857                    1527                    6388
  49                                  1626                    6688
  50                  
  51                  
  52                  
  </code></pre>
  
  <p><strong>The chart:</strong></p>
  
  <p>The Combo Chart is supposed to show output vs plan.  The plan part of it are cuml-sum bars and the output is a cuml-sum line.  As long as the output line is above the bars, we know were 'all good'.</p>
  
  <p>X axis is Work Week</p>
  
  <p>Y axis is Qty</p>
  
  <p><hr>
  First of all, there is a problem that I saw originally in that Table A &amp; B use different Rows identifiers.  Table A's rows are by serial numbers, Table B's is by work week.  </p>
  
  <p>I need to find the total number of serial numbers that were finished in each given work week.  For Q4, Work Week's (WW) will be 40-53.  Table A has a WW column.  I pivoted &amp; totaled the Table A's data for each work week, and placed this new column into Table B.  Everything is working just fine until there is a week where no output took place.  </p>
  
  <p>This pivoted column (the Tlines) defaults to use a 'null' value to represent a week where there is no output.  </p>
  
  <p>No problem right?  Well...what happens is for WW42 for example, a line is drawn from WW41 to WW43 and has a slightly increasing slope.  </p>
  
  <p>What i <em>need</em> to show is a flat line from WW41 to WW42 because the machines were down (no output).  From WW42 to WW43 however, overtime was needed to catch up and the line should have a very steep slope (maxed output).</p>
  
  <p><strong>Needs:</strong>
  I need an expression that will test if a value is null, if it is - use last weeks numbers, else use this weeks numbers.</p>
  
  <hr>
  
  <p>Here is what I tried first, I Pivoted, then calculated and replaced the column with this expression:</p>
  
  <p>If(T2Line) is null,
   Sum(T2Line) OVER (Previous([Work Week])),
  else
  Sum(T2Line)</p>
  
  <p>-does not work.</p>
  
  <p>also tried this by R-clicking on the category selectors, selecting 'custom expression' (2 columns here):</p>
  
  <p>-->first column
  Sum(PlanT2Line), </p>
  
  <p>--> second column expression
  Case  when Sum(T2Line) IS NULL THEN 
       Sum(T2Line) OVER (Previous([Work Week])) else 
  Sum(T2Line) END</p>
  
  <p>together looks like this:
  Sum(PlanT2Line), Case  when Sum(T2Line) IS NULL THEN 
       Sum(T2Line) OVER (Previous([Work Week])) else 
  Sum(T2Line) END</p>
  
  <hr>
  
  <p>Neither expression works.  After looking into how pivoting works, i believe pivoting was killing the 'node navigation'.  The 'Previous()' method looks for the previous node, but since I pivoted this information from another Table into Table B, I believe this is why it's not working.  There is no hierarchy anymore. </p>
  
  <p>Which is what I tried next.  I tried to create a custom hierarchy with Fiscal Year (FY) and Work Weeks (WW) below that. Used FY, WW on the x axix.
  Still no dice. </p>
  
  <p>Am I missing something?</p>
  
  <p>I'm feeling like I may need to <em>set up</em> the actual data table somewhere else and <em>then</em> import it so that I don't need to use Spotfire to pivot the data, thus loosing the 'previous node'.  If I do this it means more time from another dept. that needs to set this up for me.  </p>
  
  <p>So close.</p>