Time Functions when the date periods are not even

[sharpedogs]

Hi, 

I am getting 1 to 2 files a week. They don't come on the exact days each week. The date I'm using is coming from the name of the file as that the date of the data extract. I want to create some trending views but i can't use the regular functions like last week ect.. because of the gaps in the dates between files. 

Essentially each file is it's own download and date and they are all appended together. I want to be able to show in a table the latest file and then the one before that. I'm not sure how to reference create time calculation to say the closest date before this one? 

[lbendlin]

That's what I was describing.  You can apply additional filters to LASTDATE().  Say you calculate the LASTDATE() but with a filter to say that it needs to be before the last observed date. That will give you the second to last date etc.

Or you go all crazy with something like MIN(TOPN,2,,desc) 🙂

[lbendlin]

- use MAX() or LASTDATE() with a filter to exclude the current (or max) date to identify the "second last" date

- think about interpolating between reports to compute the value as it would have likely been at weekly intervals etc.

[sharpedogs]

MAX () is interesting.. I've played alot around with lastdate(). The issue I have is that the LastDate works for the last file, but then the one before that might be 3 days or 4 days or 7 days. Because the times vary i can't use a specific -7 days. I need it reference the last date without saying go back X days.

Something like a lastdate, excluding the current lastdate. i'm just not sure of the context.