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: 
Anonymous
Not applicable

Find value for qty on last date for every item

I have this warehouse database using MariaDB and want to show all stock for every item in every warehouse in Qliksense.

  This is list_item_storage table to show relational from product to warehouse

storage_iditem_idlocation_id
1Product AGBJ
2Product AFIN
3Product BGBJ
4Product CGBJ

And this is list_item_tx table to show all in and out transaction related to every product in all warehouse.

   

tx_idtx_datestorage_idstart_qtychange_qtyend_qty
110/18/2018 12:48:231029002900
211/8/2018 10:33:12129002653247
310/18/2018 12:48:563019881988
411/8/2018 10:39:11319881981790
510/18/2018 12:48:2740280280
611/8/2018 10:41:03428056224
710/18/2018 12:48:022000

I want to make something like below. But I can't get the right value to show the stock value. According to DB Admin, the logic is to grab all of the storage_id for every product in list_item_storage table. And then grab the storage_id with the latest date (tx_date) then show the end_qty in list_item_tx.

Capture.JPG

My problem is I can't get the right value for the latest stock for every product in every warehouse.

I tried several expression such as:

sum({<tx_date={"$(=max(tx_date))"}>}end_qty)

sum(if(tx_date=Max(tx_date),end_qty))

FirstSortedValue(if({<tx_date={"$(=max(tx_date))"}>}end_qty))

and even using Aggregation functions such as ONLY or MaxStrings and none of them succeed. Most of it ended with the result of showing NULL or showing only the last row value of the table.

Please help. Thanks in advance

1 Solution

Accepted Solutions
sunny_talwar

Since there are item_name and location_name in the chart... I would rather use this

FirstSortedValue(Aggr(Sum(end_qty), item_name, location_name, tx_date), -Aggr(tx_date, item_name, location_name, tx_date))

View solution in original post

10 Replies
Anil_Babu_Samineni

Is that tx_date is the Date field? Try this way?

If(tx_date = Max(TOTAL tx_date), Sum(end_qty))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

So, in your sample data... Product A have 2 different storage_ids.... do you want to pick the latest date across both storage_ids or do you want sum the latest value from each storage_id and display that?

Anonymous
Not applicable
Author

Hi Sunny, I need to show latest date of end_qty for both storage_id. If there are 3 storage_id for a single product, it means the product are in 3 different storage and I need to show 3 of it individually.

sunny_talwar

May be try this...

FirstSortedValue(end_qty, -tx_date)

Anonymous
Not applicable
Author

Hi Anil, yes, tx_date is Date field.

I've tried our suggestion and it shows NULL for all product

Capture2.JPG

Anil_Babu_Samineni

May be this?

FirstSortedValue(Aggr(Sum(end_qty), tx_date), -tx_date)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Since there are item_name and location_name in the chart... I would rather use this

FirstSortedValue(Aggr(Sum(end_qty), item_name, location_name, tx_date), -Aggr(tx_date, item_name, location_name, tx_date))

Anonymous
Not applicable
Author

Hi Sunny,

Hi Anil,

I'm currently at home during weekend and unable to access the latest DB data to make sure if the expression fetch the data I need. I will try all of your suggestion on Monday and will get back to you all immediately. Thank you very much. Happy weekend.

Anonymous
Not applicable
Author

Thanks Sunny. It's works like a charm. This is just what I need. BTW, what will be the difference if I'm using item_name & location_name and not using it in the expression. It seems that it return with same correct value whether I'm using it or not. I assume it will be more correct if I'm using it, but I'm not really sure what will happen if i'm not.