Set a variable to include everything up to yesterday's date.

[shinnickr]

Hi,

I am hoping to set a variable to include every day up to yesterday.  My field starts in 2012 and I want to include every day from 2012 -  yesterday.  I have a variable for yesterdays date 'Date(Today() -1)' but I want to include everything up to and including that date.  I don't know if I'll need some set analysis or if there's a function that would help me with that so I would appreciate any help that can be given.

Thank you very much.

[sunny_talwar]

Suggest you to try to get the other syntax work because this is not very efficient compared to the other one . But glad something worked for you

[shinnickr]

Thanks for the help and advice!  I will continue to work on it and see if I can get anything better 😃

[vishsaggi]

Yes, so can you upload your file as Sunny asked. So we can look into it.

[shinnickr]

Ok I was working on that when I had to take a break.  I will see what I can come up with!

[shinnickr]

I hope this works.  I scrambled all the data except for the Service Date field.

I did notice that the current filter was pretty inefficient when viewed in WebView.  Took quite awhile to set up the filter.  Thank you so much for any help you're able to provide and let me know if I uploaded this incorrectly or if you need more info!

[sunny_talwar · Accepted solution]

This worked

='=[Service Date] < Today()'

[shinnickr]

Golly you're good!  That seems to work and is faster to load than the other.  Thank you thank you thank you!

If I make ='=[Service Date] < Today()' a variable will it show that in the current selections instead?

Right now it's showing the formula in the current selections.  We're making this for some less-than-technical end users and don't want them to be scared or confused by the formula.  Any chance you know any way around that?

[sunny_talwar]

The only way I can think about doing this would be to use an alternative to current selection box

Current Selection Alternatives

[Anonymous]

Hi Ryan,

sorry, I didn't see previous posts (loaded later...) when I published my answer...

Maybe you have already solved all, however I must say I thought you needed a "filter" for dates in order to express something else, for ex. Sales, Orders... so I wrote you the expression with Sum, meaning Field was Sales or Orders...

In the expression you wrote above, you are doing sum of all dates and then you are reading this sum as a date, thus is big and quite wrong.

Hope you have solved your problem.

Bye

IB

[shinnickr]

Looks like this only changes the Field Names, not the selection.  I'm hoping to turn =[Service Date] < Today() into something like Through Today in the current selections.  I don't know if that's even possible though.