topic sum the data based on the last date in QlikView

sum the data based on the last date

vireshkolagimat — Thu, 11 Jan 2018 07:52:17 GMT

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.

Re: sum the data based on the last date

zhadrakas — Thu, 11 Jan 2018 08:22:48 GMT

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)

Re: sum the data based on the last date

vireshkolagimat — Thu, 11 Jan 2018 08:31:38 GMT

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.

Re: sum the data based on the last date

zhadrakas — Thu, 11 Jan 2018 08:42:57 GMT

add this to your script

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

Re: sum the data based on the last date

tresB — Thu, 11 Jan 2018 09:51:00 GMT

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

Re: sum the data based on the last date

zhadrakas — Thu, 11 Jan 2018 10:06:47 GMT

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

Re: sum the data based on the last date

vireshkolagimat — Thu, 11 Jan 2018 10:16:09 GMT

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

Re: sum the data based on the last date

tresB — Thu, 11 Jan 2018 10:21:52 GMT

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.

Re: sum the data based on the last date

zhadrakas — Thu, 11 Jan 2018 10:22:11 GMT

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

Re: sum the data based on the last date

vireshkolagimat — Thu, 11 Jan 2018 10:34:18 GMT

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.

Re: sum the data based on the last date

tresB — Thu, 11 Jan 2018 10:40:28 GMT

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;

Re: sum the data based on the last date

vireshkolagimat — Thu, 11 Jan 2018 11:10:00 GMT

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.

Re: sum the data based on the last date

zhadrakas — Thu, 11 Jan 2018 11:25:30 GMT

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

Re: sum the data based on the last date

tresB — Thu, 11 Jan 2018 11:28:21 GMT

Try doing this correction:

firstsortedvalue(TransactionQty, -StockDate) as ClosingStock

Re: sum the data based on the last date

vireshkolagimat — Sun, 14 Jan 2018 09:37:48 GMT

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.

Re: sum the data based on the last date

zhadrakas — Mon, 15 Jan 2018 06:35:12 GMT

can you share a sample qvw of that Situation?