Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
you have to use Set analyse:
sum({$<StoreDate = {$(vLatestDate)}>} StockLevel)
Where you declare a variable vLatestDate = max(StoreDate)
Good luck,
Jonathan
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
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
Hi Jonathan, sorry to be a pain...
I cannot get it to return anything but '0'. Any chance you could check my sample attached ?
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
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.
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
Hi, thanks again, but that didn't seem to make a difference.
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