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]

What is the expression you are using to implement this. May be create two variables like below and use them in your set analysis.

Sum({< DateField = {">=$(v2012) <= $(vYesterday)" } >} Yourdimfield) something like this.

[shinnickr]

Thanks!  I will use this and give it a shot and work on it from there.

[sunny_talwar]

You might be missing equal signs here:

Sum({<DateField = {">=$(=v2012) <= $(=vYesterday)"}>} Yourdimfield)

[Anonymous]

Hi Ryan,

what are you looking for, exactly?

If you need a filter in script, you can use something like:

     let vYesterday=today()-1;

     load *

     from [...]

     where Date<=$(vYesterday);

On the other hand, if it is a layout matter, you can use set analysis in a chart or table, for ex.: Date as Dimension, sum({<Date={"<=$(vYesterday)"}>} Field) as Expression.

I hope it helps.

IB

[vishsaggi]

Oops thanks Sunny. Got it.

[shinnickr]

Thanks for the help.

I got my analysis sort of working, but not with the intended output.

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

and using a list box to just show my options I get:

So I don't think I want a sum of all the dates, I just want to list the dates.

[sunny_talwar]

"Using a list box to just show my options I get:"???? Not sure I understand this? What are you expecting to see?

[shinnickr]

Hi Irene,

Thanks for the help.

I do need this to be with set analysis in a chart or table.  I have tried going forward with what you'd suggested and got to =date(sum({<[Service Date]={"<=$(vYesterdaysDate)"}>} [Service Date]))

When I do that, though, I get a single date, not a list, of 09/13/13381567.  Pretty big year.... Not sure what's happening there.

[shinnickr]

I am just using a list box to show my current options for the expression.  So what I'm hoping to see is all my dates listed from 2012 up to yesterday.  I was just using it as a visual tool to see if my expression was working.

For instance, if I make a list box with my date field I'm working with [Service Date] I get:

This is every date listed out, including dates I don't want.

What I was hoping to see with the list box with my expression in it is all the dates from 2012 to the current date.