Displaying only yesterdays Date

Katherine_dannenfelser.1
5 yrs ago
5replies
Answered

The data my company collects is a day behind, is there a way to show just the data information from yesterday? Ex: Today is 4/10, the most recent data I have and want to show in the discovery is 4/9? But I need it to always shows yesterdays date.

We can not use the last date function because our data is set to have dates all the way through 12/31/2019. I tried using the AddDate function then for the integer did (-1), However that did not work either.

Any Ideas would be helpful.

- imbarmarinescubar_pyram
- 5 yrs ago
- Reported - view
Hi Katherine Mobley ,

You can calculate "today" for a given column. Then use the AddDays function.

1. Create "New Formulation" -> "List"

2. Go to script mode

3. select the column of your dates

4. type this:

AddDays(CurrentPeriod([transactions].[dateKey]), -1)

The currentPeriod function creates a member of "today" for date columns and "now" for date-time columns (where "now" is today+the current time).

Hope this helps,

Imbar
- Katherine_dannenfelser.1
- 5 yrs ago
- Reported - view
imbar.marinescubar@pyram Thank you for your response. After testing this I am still running into problems. Is there another number besides (-1) to add into this function to pull back yesterdays date?
- imbarmarinescubar_pyram
- 5 yrs ago
- Reported - view
Hi Katherine Mobley ,

Could you attach some images of the data (the column and elements that you are using) and the script that you created?
Also which data source are you connected to? OLAP Cube, SqlServer, Pyramid-In-Memory, Oracle...?
- Katherine_dannenfelser.1
- 5 yrs ago
- Reported - view
Hi imbar.marinescubar@pyram

I am connected to the Pyramid-In-Memory data source. The column I am trying to use id DimDate.Date.

The script I created was the same as yours

AddDays(CurrentPeriod([DimDate].[Date]),-1)

The DateTT List is the one where I developed your formula. It does not work on my data set.

Also as I mentioned in the beginning I am trying to obtain Yesterdays date not today's date.
- imbarmarinescubar_pyram
- 5 yrs ago
- Reported - view
Hi Katherine Mobley ,

I believe that the column type is date-time and not date.

That means that "current period" is actually generating today's date but also the current time (say, 04:15 PM as I'm typing).

So we'll use another method to get today without the time.

there are 2 functions called date() and dateTime() to get today's date or the current time (date and hour).

We'll use date() obviously.

And we'll use another function that converts a string of the date into a timestamp for the data source to use.

To get an element from that calculation we use the function strToMember.

All together you should write:

AddDays(strToMember([transactions].[dateKey],dateToTimestamp(date())), -1)

where you replace [transactions].[dateKey] with your column.

Hope this solves everything,

Imbar