Help with expression for showing latest details

[hopkinsc]

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]

Hi,

here I try:

You probably recognize the part which is close to your original expression:

Aggr(NODISTINCT Max(LastSoldDate),[Product Code], StoreNumber)

--> Return the set of  max. LastSoldDate over Product Code and StoreNumber.

The difference to your code is NODISTINCT, because

If the expression argument (Max(..)) is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure

Your original code missed to return values on multiple appearances of combination of Product Code and Store Number.

See the 9th column of attached app to illustrate the issue.

With my code (column 10), you will notice that you will get a list of identical values. I used Max to get one (and to be sure, to get the youngest date).

(in your original code, you used firstsortedvalue, but this function's help text states:

If more than one value of expression share the same lowest sort-order, the function will return null.)

Remember that we are still evaluating that expression for every row. So next, compare the found max with the LastSoldDate value of that row. If equal(see column 11), return the date. This just as input for comparison with

StoreDate.

If this also equals, that means we have found the row with the max LastSoldDate as well as equal StoreDate.

Success, return Stocklevel (column 12).

Hope this helps,

Stefan

P.S: Noticed that "you will notice that you will get a list of identical values. I used Max to get one (and to be sure, to get the youngest date)." is not

absolutely correct, because you will already get only one value back from the aggregation. So the first Max in my expression isn't really needed.

edited by swuehl

[hopkinsc]

Hi, We have just loaded the app onto our sever and i have noticed that i am not getting the results i am expecting..

Having looked at the expression again, i notice that it it giving me the latest [LastSoldDate] then finding the line with a matching [StoreDate].

if(If(Max(Aggr(NODISTINCT Max(LastSoldDate),[Product Code], StoreNumber)) = LastSoldDate, LastSoldDate) = StoreDate, StockLevel)

I don't know why i didn't notice it before but that is not what i am looking for...

I want the expression to find the latest [StoreDate] then find the latest [LastSoldDate]

Eg.

The first part of the expression needs to find the latest [StoreDate], so in the below example, the results will be all entries with a [StoreDate] of 03-08-11

Customer            StoreDate           Product Code    Desc              Stock Level              LastSoldDate

Store A                01-08-11                1234                      Coca Cola            8                             25-07-11

Store A                01-08-11                1234                      Coca Cola            7                             23-07-11

Store A                01-08-11                1234                      Coca Cola            6                             26-07-11

Store A                02-08-11                1234                      Coca Cola            3                             28-07-11

Store A                02-08-11                1234                      Coca Cola            24                           23-07-11

Store A                02-08-11                1234                      Coca Cola            20                           20-07-11

Store A                03-08-11                1234                      Coca Cola            15                           27-07-11

Store A                03-08-11                1234                      Coca Cola            28                           23-07-11

Store A                03-08-11                1234                      Coca Cola            25                           01-08-11

Store A                03-08-11                1234                      Coca Cola            20                           02-08-11

Next i want the expression to find the latest [LastSoldDate] from the earlier results of [StoreDate] and just display that line.

So in this example above, the only line that i want to be shown is the bottom line. [StoreDate] = 03-08-11 and [LastSoldDate] = 02-08-11.

any help would appreciated.

thanks

[swuehl]

Hi,

I think it could be something like:

=if(Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate, StockLevel)

Could you give me an example to validate with your posted app? The Product code 1234 doesn't seem to exist.

Stefan

[swuehl]

I noticed some duplicate entries per Product and store (using my last posted file) with the above given expression, which I could not explain myself at the moment, but this fixed it:

=if(Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate

and Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, StockLevel)

[hopkinsc]

hi Stefan, that worked great, thanks for your help again.

I have noticed another problem though, the data i get is almost perfect, the problem there are products which have never been sold so there is not a [LastSoldDate] on some products which is causing the expression not to show those items.

Is there a way to rectify this? maybe if no [LastsoldDate] is available then just go with the latest [StoreDate]??

Thanks

[swuehl]

Hi,

do these items always show '?' as LastSoldDate?

Then maybe you could use this:

=if((Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate or LastSoldDate='?')

and Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, StockLevel)

[hopkinsc]

Hi again,

That worked, although i now get multiple lines on some items. See below...

i am wondering if there is a way to only display the lines with ? as a [LastSoldDate] if no other [LastsoldDate] is available? So in the bottom line for example, as there is a [LastSoldDate] available for that product, the one above would be hidden?

And to answer your qustion, if there is no [LastSoldDate] available, then it will always be '?'.

Thanks

[swuehl]

I tried to accomplish that with using only the lates store date. It seems that there might be multiple entries per store date. Can we distinguish them e.g. by using a timestamp instead of a Date?

[swuehl]

Added a check for single LastStoreDate:

=if(count(Aggr(NODISTINCT LastSoldDate,[Product Code], StoreNumber))=1,StockLevel,

if((Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate or LastSoldDate='?')

and Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, StockLevel))

It's getting hard to read, I think I need to rethink an rework.

One question: Is is possible that an older StoreDate holds a more recent LastSoldDate than a younger StoreDate record? Or will the max(LastStoreDate) always be part of the youngest StoreDate record?

Stefan

[hopkinsc]

Hi again. Unfortunately at the moment we do not have a time stamp field on the file we load in, only a date.

There would be multiple entires with the same StoreDate if different stock adjustments were done on the same product the same day.

There is a timestamp available on our latest version of software but only half of our customers are on that version, so we cannot load that field in as qlikview will fail when loading in files from the older customers (as it would not be able to find the TimeStamp).

If there is no other way of doing it then i will use your earlier expression which shows duplicate entries..

Is there a way i can put a dummy [LastSoldDate] (eg. 01/01/70) against the products which ='?'