Pick Up Previous Day Closing Value

[pkelly]

I am trying to create a Pivot Table that looks like the attached:

Cell E1 represents the current on hand stock.

Cell F11 picks up the calculated value from Cell E8.

I have tried the following formula:

=IF(fow_Date = varToday, SUM({<pro_FamilyCode = {'01','02'}, fow_Date = {'$(varToday)'}> } fow_InventoryOnHand) , ABOVE(Stock))

=IF(fow_Date = varToday, SUM({<pro_FamilyCode = {'01','02'}, fow_Date = {'$(varToday)'}> } fow_InventoryOnHand) , ABOVE(7))

=IF(fow_Date = varToday, SUM({<pro_FamilyCode = {'01','02'}, fow_Date = {'$(varToday)'}> } fow_InventoryOnHand) , ABOVE(COLUMN(7)))

And also the Below equivalents.

I am getting todays inventory but am not able to pick up the previous days closing stock value

.

Any assistance greatly appreciated.

[Anil_Babu_Samineni]

What is varToday??

[pkelly]

varToday is a variable = todays date.

[Anil_Babu_Samineni]

Try some thing like below

=If(fow_Date = varToday, SUM({<pro_FamilyCode = {'01','02'}, fow_Date = {'$(varToday)'}> } fow_InventoryOnHand) , Above(Sum(Stock)))

[pkelly]

Thanks - unfortunately, that didn't work - I am getting an error in expression.

For info:

Stock is calculated column =

=(Column(1) + Column(2) + Column(3) + Column(4)) - Column(5)

[Anil_Babu_Samineni]

In fact - Above(Simple data points) won't work. You must use Aggregation for that. If you want to work around, I wold ask you to share sample application

[sunny_talwar · Accepted solution]

Seems like a pivot table to me... do you need Before/After?

=IF(fow_Date = varToday, SUM({<pro_FamilyCode = {'01','02'}, fow_Date = {'$(varToday)'}> } fow_InventoryOnHand) , Before(Stock))

[pkelly]

Thanks Sunny, this worked - forgot about Before/After.