topic Re: Pick Up Previous Day Closing Value in QlikView

Pick Up Previous Day Closing Value

pkelly — Mon, 10 Jul 2017 07:34:07 GMT

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.

Re: Pick Up Previous Day Closing Value

Anil_Babu_Samineni — Mon, 10 Jul 2017 07:55:23 GMT

What is varToday??

Re: Pick Up Previous Day Closing Value

pkelly — Mon, 10 Jul 2017 07:59:16 GMT

varToday is a variable = todays date.

Re: Pick Up Previous Day Closing Value

Anil_Babu_Samineni — Mon, 10 Jul 2017 08:01:30 GMT

Try some thing like below

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

Re: Pick Up Previous Day Closing Value

pkelly — Mon, 10 Jul 2017 08:21:21 GMT

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)

Re: Pick Up Previous Day Closing Value

Anil_Babu_Samineni — Mon, 10 Jul 2017 08:33:04 GMT

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

Re: Pick Up Previous Day Closing Value

sunny_talwar — Mon, 10 Jul 2017 08:57:15 GMT

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))

Re: Pick Up Previous Day Closing Value

pkelly — Mon, 10 Jul 2017 09:14:41 GMT

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