Discussion board where members can get started with QlikView.
Hi, i am trying to display the current stock level for every product (for the selected department) for every store.
I have had some help with this before but still can't get it right.
My chart currently shows multiple lines for most of the products (this is because every store sends in a file every night detailing their stock details, so QLIKVIEW is showing me every occurence of the product). I only want to see the last file date that was processed (the field is StoreDate).
I want to see 1 line for each product showing the last file processed with the latest LastSoldDate but it has to display the accurate StockLevel for each product.
I have attached a sample.
Can anyone help please?
Solved! Go to Solution.
Suppressing the zeros will also eliminate the rows with StockLevel = zero or 0.
I think that's not what you or your customers need.
To get rid of the duplicate rows, you must remove the StoreDate from the dimensions (but you can add it to the expressions). In my opinion, you should only use the fields as dimension that you need to group your data. Could be StoreNumber(or Customer) and ProductNumber.
Then, my expression is not correct, we must put the sum around the if-clause:
=sum(if(Date(aggr(NODISTINCT max(StoreDate),[Product Code],Customer))=StoreDate,StockLevel))
Looks much better to me.
edit: If you add StoreDate as expression, you need a
, to only show the max StoreDate, else you get a '-' on rows with multiple StoreDates.
edited by swuehl
Hi Sunil, Thanks for your reply.
The sample you have attached still show multiple lines per product because there are different LastSoldDates.
I need it to only display the line with the LastSoldDate as well as the latest StoreDate..
Hi, thnks, but that still doesn't work as Maxstring will give me the higest stock level. I don't want the highest, i want the last one received and processed.
I have attached a sample again...
The bottom chart shows every product for every date.
If you look at the top chart (with your expressions) you will see that RIZLA CIGARETTE PAPER RED has a stock level of 101
But if you look at the bottom chart, the ACTUAL stock level for that product is 32 (the one with storedate 26/07/11 and lastsolddate of 26/07/11