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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

How to only display CURRENT details

Hi, I am trying to create a chart that displays CURRENT stock levels of products in our retail stores. I only want to display the latest record regardless of the date selected but users can make other selections such as Retiler or Dept Name etc.

I am guessing it would use the latest file received and processed going by the date field (which is StoreDate)

Basically i want it to ignore any selection to do with date (any made from the top row), but i want it to use any selction made from the left hand side.

I have attached a sample of my document.

Can anyone help me please?

Thanks in advance.

Chris

11 Replies
Not applicable

you have to use Set analyse:

sum({$<StoreDate = {$(vLatestDate)}>} StockLevel)

Where you declare a variable vLatestDate  = max(StoreDate)

Good luck,

Jonathan

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, thanks for your reply..

That expression doesnt seem to work. It only displays zero's.

and where in the set analysis does it say to ignore and date selections?

Thanks

Not applicable

Hi,

The set analyse on th StoreDate forces the selection to the variable value. You should check that the max(storedate) gives you the right value.

Regards,

Jonathan

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Jonathan, sorry to be a pain...

I cannot get it to return anything but '0'. Any  chance you could check my sample attached ?

Not applicable

Hi,

I think there is an interpretation issue with your date field. Tried sorting it out but didn't succeed yet. Have not directly time to continue to look but I would start by making sure your StoreDate is interpreted as date, therefor in load process do Date(StoreDate) as StoreDate.

Maybe later I can have a look in more detail,

Jonathan

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Jonathan, thanks for your help. Would there be any other way of achieving what i want? If i use the 'Date' function in the script, it would not be optimised. We have over 150million records so an optimised load is a must.

Thanks again.

Not applicable

Hi,

'

you can try to force teh SET DateFormat  statement to equal the format of StoreDate (nopw they do not match I think).
You have currently SET DateFormat = 'DD-MM-YYYY'; while if I display StoreDate it shows 01/07/2011.
Try Set DateFormat = 'DD/MM/YYYY'; in the load.s,

Regards,

Jonathan

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, thanks again, but that didn't seem to make a difference.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

It seems that i am trying to do the wrong thing!! Sorry for wasting your time. I will put up another post with the right question