sum the data based on the last date

[vireshkolagimat]

Hi All,

I want to show the closing stock by warehousecode and productcode and date wise.

But when i try to sum it, if there is only one date, i am getting the closing stock as expected. But if there are multiple dates available i need to only the last value as closing stock for that particular data but i am getting all the data.

Please have a look at the attached sample qvw.

Thank you.

[tresesco]

Try like:

Directory;

LOAD ProductKey,

     WarehouseCode,

     ProductCode,

     StockDate,

     OpeningStock,

     TransactionQty

FROM

[InvCalculation.QVD](qvd);

Join

Load

          WarehouseCode,

          ProductCode,

          firstsortedvalue(ClosingStock, -StockDate) as ClosingStock

FROM

[InvCalculation.QVD] (qvd)

group by WarehouseCode, ProductCode;

If you want to consider the Day field as well in the result (like your UI result), you might have to create and include the same in joining table and group by clause;

[vireshkolagimat]

Hi tried the above method but getting wrong value. for 12/31/2017 the closing stock value is 1275.084 but i am getting almost double the data.

Attaching the qvw for your reference along with sample data.

Thank You.

[zhadrakas]

try this script:

data:
LOAD ProductKey & StockDate as KEY,
ProductKey,
WarehouseCode,
ProductCode,
StockDate,
day(StockDate) as day,
OpeningStock,
TransactionQty,
ClosingStock
;

LOAD * INLINE [
ProductKey, WarehouseCode, ProductCode, StockDate, OpeningStock, TransactionQty, ClosingStock
A01-1110001, A01, 1110001, 31/12/2017, 0, 1275.084, 1275.084
A01-1110001, A01, 1110001, 09/01/2018, 1275.084, 0, 1275.084
A01-1110001, A01, 1110001, 09/01/2018, 1275.084, -2, 1273.084
A01-1110001, A01, 1110001, 09/01/2018, 1273.084, 0, 1273.084
A01-1110001, A01, 1110001, 09/01/2018, 1273.084, -2, 1271.084
A01-1110001, A01, 1110001, 09/01/2018, 1271.084, 0, 1271.084
A01-1110001, A01, 1110001, 09/01/2018, 1271.084, -3, 1268.084
A01-1110001, A01, 1110001, 09/01/2018, 1268.084, 0, 1268.084
A01-1110001, A01, 1110001, 09/01/2018, 1268.084, 5, 1273.084
A01-1110001, A01, 1110001, 09/01/2018, 1273.084, 11, 1284.084
A01-1110001, A01, 1110001, 09/01/2018, 1284.084, 7, 1291.084
];

LastValue:
load
ProductKey & StockDate as KEY,
LastValue(ClosingStock) as LastClosingStock
Resident data
Group by ProductKey, StockDate

then the expression

sum(LastClosingStock) Should return the desired value

[tresesco]

Try doing this correction:

firstsortedvalue(TransactionQty, -StockDate) as ClosingStock

[vireshkolagimat]

Hi, It works fine if test the above script independently. When i use the output of this script, the values are being added u and i am getting the cumulative sum instead of the day end stock value.

Thanks for the suggestion.

[zhadrakas]

can you share a sample qvw of that Situation?