Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Eldinkarisik
Contributor III
Contributor III

Retrieving the lastest stock amount of a specific locationID

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: 

DatelocationIDProductIDQTY
1-1-2018AX500
25-1-2018AX600
5-3-2018AX55
9-5-2018AX666
5-1-2019AX99
5-1-2019AX99
9-1-2018BX546
15-1-2018BX154
19-3-2018BX234
16-5-2018BX2146
16-5-2018BX2146
20-1-2019BX22156
1-1-2018AY55
25-1-2018AY66
5-3-2018AY1547
9-5-2018AY21456
5-1-2019AY223
9-1-2018BY589
15-1-2018BY246
19-3-2018BY246
16-5-2018BY288
16-5-2018BY288
20-1-2019BY66

 

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

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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

image.png

View solution in original post

8 Replies
sunny_talwar

Try this expression

FirstSortedValue(DISTINCT QTY, -Date)

With no selection

image.png

When Jan-2018 is selected

image.png

Eldinkarisik
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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: 

DatelocationIDProductIDType ZQTY
25-1-2018AXZ600
15-1-2018BXZ154
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
Contributor III
Contributor III
Author

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

sunny_talwar

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

image.png

Eldinkarisik
Contributor III
Contributor III
Author

Thank you for the quick help. This indeed helps!