Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

hopkinsc
Valued Contributor II

Help with expression for showing latest details

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Help with expression for showing latest details

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

47 Replies
hopkinsc
Valued Contributor II

Help with expression for showing latest details

Morning everyone, anyone have any ideas on this please?

SunilChauhan
Esteemed Contributor

Re: Help with expression for showing latest details

Please see the attached qvw .

this might help u

Thanks

Sunil Chauhan

hopkinsc
Valued Contributor II

Help with expression for showing latest details

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
Esteemed Contributor

Re: Help with expression for showing latest details

Please see te attached

Thanks

Sunil Chauhan

hopkinsc
Valued Contributor II

Re: Help with expression for showing latest details

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

SunilChauhan
Esteemed Contributor

Help with expression for showing latest details

in stock level  expression write Maxstring(Stock level)

Thanks

Sunil Chauhan

hopkinsc
Valued Contributor II

Re: Help with expression for showing latest details

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
Esteemed Contributor

Help with expression for showing latest details

Take minstrring instead of maxstring

hopkinsc
Valued Contributor II

Re: Help with expression for showing latest details

but wouldnt that give me the lowest stock level??

I need the ACCURATE stock level.