Filter for Yesterday

[system]

I am trying to create and schedule a daily report. I am wanting the report to go out each morning displaying loans that have Date Conversion being the day before. For example, tomorrow morning 4/17/15, the report would show all loans with Date Conversion 4/16/2015.

I thought I could just add Date Conversion to the filter area at the top and make it equal to CurrentDate-1, but that is not working.

Also, I cannot do anything to the universe. I just need a way to do this in the report itself.

Any help is appreciated greatly.

---

BOgohanBO (BOB member since 2015-03-16)

[system]

in the past, I wanted to get previous month to date numbers, and was told to:

1. Create a variable “Prior Month”
   =If(MonthNumberOfYear(CurrentDate())=1;12;MonthNumberOfYear(CurrentDate())-1)

then

2. Create a variable “Prior Month Flag”
   =If(DaysBetween([Date Funding];CurrentDate())>365;“False”;If(MonthNumberOfYear([Date Funding])=[Prior Month];“True”;“False”))

then

place the “Prior Month Flag” variable in the report filter and have it = true

This worked for previous month to date, but I am not sure how to format this and use the same strategy to get yesterday.

---

BOgohanBO (BOB member since 2015-03-16)

[system]

The suggestion for prior month still let’s your query pull all data. Isn’t it?

So my guess would be you have to add a prdefined conditon to the universe (the best, but not possible you say) or maybe there’s a freehand sql solution.

---

MBLM (BOB member since 2014-01-31)

[MarkP]

If someone else can change the universe, that is the best solution.

I’ve always used relative dates for scheduled reports. By relative dates, I mean the sort of things in this sticky in the universe section:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

If you point your universe people towards that, they may realise that it’s best practice for scheduling reports and implement it for you.

[system]

@MBLM

The suggestion for prior month still let’s your query pull all data. Isn’t it?

So my guess would be you have to add a prdefined conditon to the universe (the best, but not possible you say) or maybe there’s a freehand sql solution.

The query and gives previous MTD when it is run on the first of the new month. I was hoping someone with more knowledge than me could help me use that as a guide but make it for previous day. I just am not good enough to decipher and adjust it

---

BOgohanBO (BOB member since 2015-03-16)

[system]

@Mark P

If someone else can change the universe, that is the best solution.

I’ve always used relative dates for scheduled reports. By relative dates, I mean the sort of things in this sticky in the universe section:
DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

If you point your universe people towards that, they may realise that it’s best practice for scheduling reports and implement it for you.

Thanks for the suggestion, but as mentioned previous, universe is not an option. Not trying to be rude, but I really need a solution outside of universe changes. Thanks

---

BOgohanBO (BOB member since 2015-03-16)

[system]

Don’t get me started on the “won’t change the universe” story .

[Yesterday]
=RelativeDate(CurrentDate();-1)

[Prior Day Flag]
=If([Date Funding]=[Yesterday];1;0)

Filter on the 1’s.

---

Mak 1 (BOB member since 2005-01-06)

[system]

Thanks for understanding. We can’t help what we can’t control and have to do the best we can with what we have to work with.
I created what you suggested. Thank you - however - I am a complete newbie to this so when you said “filter on the 1’s,” can you tell me exactly how to do that?

---

BOgohanBO (BOB member since 2015-03-16)

[system]

@BOgohanBO

Thanks for understanding. We can’t help what we can’t control and have to do the best we can with what we have to work with.
I created what you suggested. Thank you - however - I am a complete newbie to this so when you said “filter on the 1’s,” can you tell me exactly how to do that?

Select table or page >Anlaysis > Filters.

Set
[Prior Day Flag] = 1

---

Mak 1 (BOB member since 2005-01-06)

[system]

I was able to solve this by adding a filter for [Date Conversion] in the query. Filter was Date Conversion - Equal to - Yetserday. Yesterday is an object we had under Relative Dates. Problem solved

---

BOgohanBO (BOB member since 2015-03-16)