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.

[vishsaggi]

Can you share your sample app and what is your expected output?

IF its  a list box just use like below in your listbox expression

= IF(DateField >= $(v2012) AND DateField <= $(vYesterday), DateField)

[sunny_talwar]

Use text box object rather then list box

[shinnickr]

I still get the same thing.  A date of '13515664-06-18'.

[vishsaggi]

Can you share your expression? Do not use Sum in your expression use IF. As mentioned in my earlier message.

[shinnickr]

I am using the expression =Date(Sum({< [Service Date] = {">=$(=v2012) <= $(=vYesterdaysDate)"}>} [Service Date]), 'YYYY-MM-DD')

And I am just using the list box as a visual tool to see if my expression is working.  I apologize for being unclear.

My end-goal is to have a filter that selects every day from 'min(DateField) to Date(Today() -1)'.  That's what I'm working towards.  I was using the list box just to see if my expression was working the way I'd intended.  Perhaps I should have been going about it a different way.

[vishsaggi]

You cant use listbox to sum the value. If you want to use sum function you have to use Textbox as Sunny mentioned in his earlier reply. If you want to see the dates use IF as i mentioned. Sum() or Count() for those dates can be displayed beside when you use expression tab and write an expression in that listbox. like here

What are you expecting in your listbox? All the dates just use IF condition and check.

[shinnickr]

What I'm hoping to see in my list box is all the days from min(DateField) to yesterday's date.  I have dates in my DateField that are in the future currently (like up to 2018), and I do not want those in the selection.  I am trying to set a filter that only shows from the beginning of the data to yesterday's date.

I tried using

=IF

([Service Date] >= MIN([Service Date])

AND

[Service Date] <= $(vYesterdaysDate), [Service Date])

but I was not getting anything returned.

[sunny_talwar]

Try this

If([Service Date] < Today(), [Service Date])

[vishsaggi]

Yes your will not cos the format i different when you use min. So use like

=IF ([Service Date] >= Date(MIN([Service Date]), 'YYYY-MM-DD'),

AND

[Service Date] <= '$(vYesterdaysDate)', [Service Date])

[shinnickr]

Perfect.  Now I'm seeing exactly what I wanted to see in my list box.

Now how do I turn that into something I can use as an OnActivateSheet trigger to select all of those dates?