Retrieving the lastest stock amount of a specific locationID

[Eldinkarisik]

Hi All, 

I have a dataset of date's, locationID's, productIDs and QTY. I am trying to create a graph and a table that illustrates an overview of the latest balance of amounts from all locationIDs and productids with the ability to select/filter on a monthyear, weekyear or year but also locationID and productID. 

For example, I have created an excel file that shows the following: 

Date	locationID	ProductID	QTY
1-1-2018	A	X	500
25-1-2018	A	X	600
5-3-2018	A	X	55
9-5-2018	A	X	666
5-1-2019	A	X	99
5-1-2019	A	X	99
9-1-2018	B	X	546
15-1-2018	B	X	154
19-3-2018	B	X	234
16-5-2018	B	X	2146
16-5-2018	B	X	2146
20-1-2019	B	X	22156
1-1-2018	A	Y	55
25-1-2018	A	Y	66
5-3-2018	A	Y	1547
9-5-2018	A	Y	21456
5-1-2019	A	Y	223
9-1-2018	B	Y	589
15-1-2018	B	Y	246
19-3-2018	B	Y	246
16-5-2018	B	Y	288
16-5-2018	B	Y	288
20-1-2019	B	Y	66

I would like to have the ability to show without a selection of a month or year a default value of the current stock balance of every location ID. For example: Location A: X 99 and Y 223 , Location B: X 22156 and Y 66. Keep in mind that there can be rows with the same date, locationID, ProductID and QTY or Same date but different amount. 

And If would select a certain month or week, I will would have the latest amount balance within that selection. For example, If I would select for the month Januari in 2018 that I would get: 

25-1-2018 A X 600; 25-1-2018 B X 66; 15-1-2018 B Y 154 ;15-1-2018 B Y 246

This also applies if a certain year is selected that shows the stock balance for every month of that year. I would like to solve this with set analysis although if not possible than in the load script. 

Hope you guys can help.

Best regards,

Eldin

[sunny_talwar]

Try this expression

FirstSortedValue(DISTINCT QTY, -Date)

With no selection

When Jan-2018 is selected

[Eldinkarisik]

Thank you for the quick reply. This expression indeed works for individual cases only. But if there are more than 1 locationIDs  it will take only the amount of the latest date qty instead of taking both of the balances of the different locationIDS and sum those up. 

Your example of selecting Jan-2018 should have considered B's latest amount of 154 and summed it up with the 600 of the latest amount of X.

[sunny_talwar]

I am not sure I understand? Would you be able to provide the exact chart you wish to see in QlikView based on selection in Jan-2018 and also when nothing is selected?

[Eldinkarisik]

Hi Sunny_Talwar, 

I have added a word file with some example graphs. I have added a field called type Z in order to make some of the graphs that shows the total balance overall. For example, the januari data of locationID A and B with productid X, are within a certain type (which I made as Z) and make in total of qty 754.

Table example: 

Date	locationID	ProductID	Type Z	QTY
25-1-2018	A	X	Z	600
15-1-2018	B	X	Z	154

[sunny_talwar]

Where did Type Z field came from? Is this part of the data? Can you share the new data as what is the value for Type Z for the rows not shown in the table example above?

[Eldinkarisik]

I have attached the excel file with the Type Z field. 

[sunny_talwar · Accepted solution]

Each chart will be a little different... but here is how you can start

[Eldinkarisik]

Thank you for the quick help. This indeed helps!