11 Replies Latest reply on Aug 15, 2018 1:01 PM by Scott Hinchman

# Calculate to display data from Yesterday or Fri-Sun if its Monday

Hello All,

I have a daily report that currently returns yesterday's data.  Over the weekend the data views don't get updated.  Monday morning at 930am it refreshes.

I want to setup this view to show Yesterday's data on any day other than Monday.

On Monday I want it to show Friday through Sunday.

I am working from a data view and not a custom SQL so I need to use calculations based on the fields I have. I cannot share a workbook.

Document Date is a date Dimension field I would be using to drive the calc.

Any help would be appreciated in accomplishing this task.

Thanks!

Scott

• ###### 1. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Clarification - The extract refreshes every day Mon-Friday at 930am.

My problem is that Monday morning when it refreshes I see only Sunday, I need to see Friday through Sunday.

• ###### 2. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Hi Scott,

What is the type of filter shown in the view is it relative date filter or range?

BR,

NB

• ###### 3. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Hi Scott,

I had a similar situation but with Check-In dates. Instead, I wanted to see check in dates for tomorrow and on Friday wanted to see check in dates Saturday-Monday. I created a formula to display or not display (there's likely a better way or more efficient way to do this but this worked for me).

I manipulated my calculation to work for your situation if you'd like to use this method.

Here's the calculation:

IF DATENAME('weekday', TODAY())= "Monday" AND [Date Field] = DATEADD('day',-1,TODAY())

THEN "Display"

ELSEIF DATENAME('weekday', TODAY())= "Monday" AND [Date Field] = DATEADD('day',-2,TODAY())

THEN "Display"

ELSEIF DATENAME('weekday', TODAY())= "Monday" AND [Date Field] = DATEADD('day',-3,TODAY())

THEN "Display"

ELSEIF DATENAME('weekday', TODAY())= "Tuesday" AND [Date Field] = DATEADD('day',-1,TODAY())

THEN "Display"

ELSEIF DATENAME('weekday', TODAY())= "Wednesday" AND [Date Field] = DATEADD('day',-1,TODAY())

THEN "Display"

ELSEIF DATENAME('weekday', TODAY())= "Thursday" AND [Date Field] = DATEADD('day',-1,TODAY())

THEN "Display"

ELSEIF DATENAME('weekday', TODAY())= "Friday" AND [Date Field] = DATEADD('day',-1,TODAY())

THEN "Display"

ELSE "Don't Display"

END

Drag on to filters and select 'Display'

Hope this helps!
Shelby

2 of 2 people found this helpful
• ###### 4. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Relative and is set to Yesterday at the moment.  When the Data refreshes is populates yesterdays data.  The reason we use this is because sometimes we get data with dates of today with the refresh so I don't set it to Max date for example.

• ###### 5. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Thank you for Sharing this, I will see how I could manipulate it to my situation.

I am hoping for something like

IF DATENAME('weekday', TODAY())= "Monday" then ...something that equals yesterday plus the day before that and the day before that....

=|

• ###### 6. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Hi Scott

I edited my response/ formula to match what you are looking for. Try plugging in your date field and see if it gets you what you need. Hope it helps!

Thanks!
Shelby

• ###### 7. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Well it works to give me display don't display but Im not sure how I would use that.  I want it to always show me data no matter what day it is.

Its just that if its Monday I want to see a range of the last 3 days and its not Monday it should point to yesterday or Today()-1 or any calc that returns that.

• ###### 8. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

You take that calculation onto filters and select Display.

Then your views/dashboard will display Fri-Sun data on Mondays, Mon data on Tuesdays, Tues data on Wednesday, Wednes data on Thursday and lastly Thurs data on Friday.

Today being Wednesday, it should only be showing the Tuesday data (8/14). The calc takes into account if it's Monday to show Friday, Saturday and Sunday.

Does that help?

• ###### 9. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

yes this makes sense

would I set my date range to the last 4 days so on Monday it can show 3 days of data and all other days it will show only one because the calc you made?

• ###### 10. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

I would just take the date range filter off. That display filter will automatically show the data you're wanting to see.

2 of 2 people found this helpful
• ###### 11. Re: Help to retrieve data from Yesterday or Fri-Sun if its Monday

Derp.  of course. thank you so much