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.

[zhadrakas]

Hi viresh,

how do you know which one the last value is.

If there is a timestamp or a Transaction ID you can try an Expression like this

sum({<StockDateTime={'=max(StockDateTime)'}>}ClosingStock)

[vireshkolagimat]

Hi, yes even i tried the below expression and it looks good.

=sum({<StockDate={$(=max(StockDate))}>}ClosingStock)

how we can make this in the edit script instead front end.

Thank you.

[zhadrakas]

add this to your script

join load
ProductKey,
StockDate,
LastValue(ClosingStock) as LastClosingStock
Resident data
Group by ProductKey, StockDate

[tresesco]

Or, I guess FirstSortedValue - script function ‒ QlikView would be better way.

[zhadrakas]

whats the difference? Should be the same in both expressions.

[vireshkolagimat]

i tried lastvalue but not getting the data as expected. Need to check with FirstSortedValue

[tresesco]

If you meant - firstvalue() VS firstsortedvalue(), then THERE ARE differences.

FirstValue() returns the value that was loaded first from the records ... // depends on load order

FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument....   // depends on the parameter you pass ; in this case it would a date field.

[zhadrakas]

thats weird. i tested it and i got the right values.

ok try FirstSortedValue. I think you Need to set the rank Parameter to -1 to get the last value

regards

tim

[vireshkolagimat]

Hi,

I used the below script but still it is combining the all values.

Directory;

LOAD ProductKey,

     WarehouseCode,

     ProductCode,

     StockDate,

     OpeningStock,

     TransactionQty,

     firstsortedvalue(ClosingStock,StockDate) as ClosingStock

FROM

[InvCalculation.QVD]

(qvd)

group by ProductKey,

     WarehouseCode,

     ProductCode,

     StockDate,

     OpeningStock,

     TransactionQty;

Let me if i missed out anything.