Data load limited to last 10 weeks

[ttomasz_pl]

Hi,

I've been searching quite a lot through discussion board for a solution but couldn't find on that would suit my needs. Maybe someone here could help me.

I have table with list of documents and each of that documents has a creation date and I need to build a report that will sum up some of the values according to week number.  There is a lot of data and it would be the best to load only lines from, let's say, last 15 weeks.

It seemed quite easy at the beginning. I just used WHERE with Week([Creation Date]) => (Week(today()-15). But that's not working when I'm, for example, in February and i go back 15 weeks it's 2016 and week should be 42 of 2016, not -10.

I'm experimenting with timestamp([Creation Date],'YYYY.MM') but not very successful Any thoughts on how to do that load?

[ahaahaaha]

Hi Tomasz,

As a variant, in parallel with field "Date" load field with function RecNo (RecNo() as ID, for example) and use this field in expressions.

Regards,

Andrey

P.S. You can generally use Num ([Field date])

[eduardo_sommer]

Hi Tomasz,

You can use something like

  Where [Creation Date] >= WeekStart(today(), -15)

Eduardo

[maxgro · Accepted solution]

I think, you can check

[Creation Date] >= (WeekStart(Today()) - 15*7)

or

[Creation Date] >= (Today() - 15*7)

[ttomasz_pl]

Hi,

Thank you for helping.

I didn't catch how would that help me with loading only last 10 or 15 weeks. If I understand correctly, those functions would add numbers to my rows. But then how would I know which of those number to load in the script? I guess it is worth to mention that my [Creation Date] field has multiply same values and different quantity for every day...

[ttomasz_pl]

Eduardo and Massimo,

Thank you for helping.

Yes, WeekStart is a function that I was looking for. Used with "15*7" sets back the date to the beginning of week 48 of 2016 (if done today 2017-03-07).