5 Replies Latest reply: Jun 6, 2011 3:20 AM by el_tyrell

# Expressions : how to get previous column value

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

• ###### Re: Expressions : how to get previous column value

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

Table:

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

[

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.

• ###### Expressions : how to get previous column value

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

• ###### Re: Expressions : how to get previous column value

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

- Sridhar

• ###### Re: Expressions : how to get previous column value

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

• ###### Expressions : how to get previous column value

Thanks Ivan !