Setting Date on Sheet activation

[Oggy172]

Hi all, 

Can't seem to get this to work properly.

I want to preset a field (LoggedDate) as Yesterdays Date. Logged Date is natively in format "DD/MM/YYYY hh:mm"

so I have the below:

	
    Calendar:
    LOAD
		date(Date,'YYYYMMDD') 		AS 	TodaysDate,
        date(Date-1,'YYYYMMDD') 	AS 	YesterdaysDate
	FROM
	[lib://QVD Data:Office_365_Sharepoint - a@b.com.com//Shared Documents/QlikSaaS/Calendar2.qvd]
	(qvd)
    where Date = Today();
    
    
    let vToday = peek('TodaysDate', -1, 'Calendar');
    let vYesterday = peek('YesterdaysDate', -1, 'Calendar');

To set the variables....

and this to grab the logfile:

[ChangeLog]:
LOAD	
	[Entry_No],
	[Date_and_Time],
   	date(floor([Date_and_Time]),'YYYYMMDD') as LoggedDate
Resident [Rest Connection Master Table]

I then have an action to "Set Field Values" for LoggedDate to =$(vYesterday) 

but it doesnt do anything, if I surround the two parts of the load script in NUM() it works, so it seems its format related, but try as I may, I cannot get it working

[-SW-]

Can you remove the = from =$(vYesterday) 

and try again?

[Oggy172]

If you mean in the set field value, then it forces it when I close the expression editor

[-SW-]

As in, remove that character "=" after the expression editor in that screenshot as you see it.

[Oggy172]

Ah, didn't think of that, sadly it's the same.... 

No filter applied... 

Would it be beneficial to upload an anonymised sample?

[-SW-]

Go for it, I can have a quick look.

[Oggy172]

This is obviously massively cut down and pointed at inline tables, as my actual copy takes the data from our Business Central 365 data

Hopefully it behaves the same!

[Adam_Romanowski]

Maybe instead using "Select values in field" try "Select values matching search criteria". It will probably work.

[Oggy172]

Hi Adam,

Yes this worked, though I am keen to know why the first solution didn't - as that seems the obvious pick (and worked when I converted the date to it's number/serial)

Thanks!!

[marcus_sommer]

I suggest to avoid always to match/calculate anything against formatted values else using only pure numeric values. 

Beside this I would tend to skip the variable stuff and using an appropriate flag-field from the calendar, for example:

DateField - today() as Flag

and then 0 means today and -1 relates to yesterday.