Spotfire: Trying to fill in a 'Null' value with the previous value in the chart from a pivoted cloumn

Question

2 Separate data tables for this analysis:

Table A: table is planned output by work week. ~What we want to happen.

and

Table B: table is actual output by serial number. ~what is happening.

Both tables are being pulled from Microsoft AX, ERP system. (SQL based) Current week =49 current Table B:

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

The chart:

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'.

X axis is Work Week

Y axis is Qty

First of all, there is a problem that I saw originally in that Table A & B use different Rows identifiers. Table A's rows are by serial numbers, Table B's is by work week.

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 & 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.

This pivoted column (the Tlines) defaults to use a 'null' value to represent a week where there is no output.

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.

What i need 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).

Needs: I need an expression that will test if a value is null, if it is - use last weeks numbers, else use this weeks numbers.

Here is what I tried first, I Pivoted, then calculated and replaced the column with this expression:

If(T2Line) is null, Sum(T2Line) OVER (Previous([Work Week])), else Sum(T2Line)

-does not work.

also tried this by R-clicking on the category selectors, selecting 'custom expression' (2 columns here):

-->first column Sum(PlanT2Line),

--> second column expression Case when Sum(T2Line) IS NULL THEN Sum(T2Line) OVER (Previous([Work Week])) else Sum(T2Line) END

together looks like this: Sum(PlanT2Line), Case when Sum(T2Line) IS NULL THEN Sum(T2Line) OVER (Previous([Work Week])) else Sum(T2Line) END

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.

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.

Am I missing something?

I'm feeling like I may need to set up the actual data table somewhere else and then 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.

So close.

"I pivoted & totaled the Table A's data for each work week, and placed this new column into Table B." - did you inserted it as a new column, or as new rows? — Máté Juhász
I inserted it as a new column. Rows in Table B are by Work Week. — Tooth
hey sorry, didn't have a chance to respond until now. can you please add a column to your sample data showing the expected result? — niko

niko niko · Accepted Answer · 2015-12-02T00:36:56

it's difficult to answer properly without some sample data. I'm assuming that you're missing data for WW42 - i.e. it looks like:

[Work Week]   [T2Line]
40            1234
41            2345
43            3456

the reason your OVER expression isn't working is because, well, it is working :) there is no value 42 in [Work Week], so Previous([Work Week]) evaluates to 41.

I've dealt with this in the past by setting up an embedded, one-column dummy table with all possible values for [Work Week] (I assume you're counting a static number of weeks every year), then joining that with your table so that you're left with:

[Work Week]   [T2Line]
40            1234
41            2345
42            
43            3456

or, I've misunderstood your question. in which case, for the sake of clarity, could you share some sample data please?

Spotfire: Trying to fill in a 'Null' value with the previous value in the chart from a pivoted cloumn

1 Answers