Solved: Help with expression for showing latest details - Qlik Community

hopkinsc · ‎2011-07-28

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?

swuehl · ‎2011-08-12

Chris, wait.

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.

Regards,

Stefan

edit: If you add StoreDate as expression, you need a

=Date(max(StoreDate))

, to only show the max StoreDate, else you get a '-' on rows with multiple StoreDates.

edited by swuehl

View solution in original post

hopkinsc · ‎2011-07-29

Morning everyone, anyone have any ideas on this please?

SunilChauhan · ‎2011-07-29

Please see the attached qvw .

this might help u

Thanks

Sunil Chauhan

hopkinsc · ‎2011-07-29

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..

Any ideas?

SunilChauhan · ‎2011-07-29

Please see te attached

Thanks

Sunil Chauhan

hopkinsc · ‎2011-07-29

That displays the dates correctly but the stock level isn't being displayed for most of the products...

SunilChauhan · ‎2011-07-29

in stock level expression write Maxstring(Stock level)

Thanks

Sunil Chauhan

hopkinsc · ‎2011-07-29

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

SunilChauhan · ‎2011-07-29

Take minstrring instead of maxstring

Sunil Chauhan

hopkinsc · ‎2011-07-29

but wouldnt that give me the lowest stock level??

I need the ACCURATE stock level.