Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Generate Dates with specific intervals within a range

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

1 Solution

Accepted Solutions
Highlighted

May be this?

Table:

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

AutoGenerate 1

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

View solution in original post

6 Replies
Highlighted

May be this?

Table:

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

AutoGenerate 1

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

View solution in original post

Highlighted
Contributor III
Contributor III

This works.Thanks Sunny

Really Appreciate your quick response

Highlighted
MVP
MVP

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.

Highlighted
Contributor III
Contributor III

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

Highlighted
MVP
MVP

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

Highlighted
Contributor II
Contributor II

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.