Expressions : how to get previous column value

[#1]

Hello,

I just made a pivot table.

Each line contains : an item number, a total stock quantity and then comes the "pivoted" data, a series of available stocks per date (or, to be more precise, per month).

Example:

ITEM     TOTAL STOCK     MONTH     0     1     2     3     4     5     6     7     8     9     10     11     12

1          1000                    300               200   150               -10         -50         

...

What I would like to do, with an expression, is the following :

If (Month = 0) then stock at Month 0 position = Total Stock

==> I can do it with the following expression : if (ColumnNo()=1,QTESTOCKTOTAL, ...)

But I'm stuck here :

If (value is empty) then get previous value

In the above example, I should get :

ITEM     TOTAL STOCK     MONTH     0     1     2     3     4     5     6     7     8     9     10     11    12

1          1000                    300   300   300   200   150   150   150   -10   -10   -50   -50    -50   -50

Which expression can I use to do that ?

Thanks !

Stéphane

[#2]

Hi there, I would do it within the script, in order to keep the expressions simple, like this:

Table:

crossTable('Month','Amount',2)

Load * inline

[

ITEM,     TOTAL STOCK,   0,     1,     2,     3,     4,     5,     6,     7,     8,     9,     10,     11,    12

1,          1000,                    300,     ,      ,   200,   150,      ,      ,   -10,      ,   -50,       ,       ,     

]

;

Table2:

Load ITEM, Month, if(ITEM = previous(ITEM) and not len(Amount), peek(InventoryAmount), Amount) as InventoryAmount

resident Table

order by ITEM, Month;

drop table Table;

Hence, the expression should be somethin like this:

sum(InvetoryAmount)

Regards.

[#3]

Thanks, I will try that next week (I'm now on holidays!)

It's a pity it's not possible to do it in the Expression.

Stéphane

[#4]

Stephane, You could try this with above() function.

- Sridhar

[#5]

Hi all, given the location of the time dimension, the above function will not work, but you can actually try with the before function, like this:

if(len(Amount), sum(Amount), before(Inventory))

However, I think that it might be harder to identify those cases in which you actually have zero stock and those in which you do not actually have any stock entry at all. This expression will certainly work with the sample data, but you will have to take some other considerations when you have more than one stock entry, per month and item combinations.

Regards

[#6]

Thanks Ivan !

Your solution seems good. I tried your script and it's working.

I will try to adapt it to my source DB table.

Best regards and thanks again !

Stéphane