Generate Dates with specific intervals within a range

[umashankarus]

Hi Experts,

If current date is 2/5/17,

how to create dates with interval of 7 days such as  >>>  1/6/17, 1/13/17, 1/20/17, 1/27/17, 2/3/17 ?

within the past 'n' days (say, past 30 days)

Thanks,

Umashankar

[sunny_talwar · Accepted solution]

May be this?

Table:

LOAD Date(AddMonths(Today(), -1) -6 +(IterNo()*7)) as Date

AutoGenerate 1

While AddMonths(Today(), -1) -6 +(IterNo()*7) <= Today();

[umashankarus]

This works.Thanks Sunny

Really Appreciate your quick response

[swuehl]

Not really sure what you were looking for, here is another approach that will always look at the past x days (not depending on the number of days in a month), and will always return a specific weekday (not dates with a specific distance from today)

AllFridaysInPast30Days:

LOAD Date

WHERE Weekday(Date) = 5; // 5 = Friday

LOAD Date(Today()-31 + Recno()) as Date

AutoGenerate 31;

You can also use more sophisticated maths to reduce the loop needed, but I think above is easier to maintain.

[umashankarus]

Thank you

I am trying to generate a trend chart
a) dimensions - with above date intervals for past 1 month
b) expression - count over a field that falls within this date range

Is there better option or pl point me to any design blog / thread that already discusses this

[swuehl]

Again, not really sure what you want to achieve.

Maybe just create a master calendar for your date field in your fact table (whatever fact you want to count in your expression), and then create a field using Weekstart() to group your Dates in 7-days intervals (you can set the weekday in the weekstart() function).

This is an approach when you actually want to group your data (group your facts in 7-days intervals).

Use a calculated dimension or set analysis to filter the data to the last x days.

The Master Calendar

[wh1009]

How can I take the end date of a range chosen in the date range picker object by a user, and use that as a reference to generate a list of dates in the range at a frequency chosen in a drop down (daily, weekly, monthly)? for example, if the range chosen is 1/1/2018-3/24/2018, and the frequency chosen is monthly, the list would be 1/24/2018, 2/24/2018, 3/24/2018.