topic Re: Generate Dates with specific intervals within a range in QlikView

Generate Dates with specific intervals within a range

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Generate Dates with specific intervals within a range

sunny_talwar — Sun, 05 Feb 2017 22:15:27 GMT

May be this?

Table:

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

AutoGenerate 1

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

Re: Generate Dates with specific intervals within a range

umashankarus — Sun, 05 Feb 2017 22:37:16 GMT

This works.Thanks Sunny

Really Appreciate your quick response

Re: Generate Dates with specific intervals within a range

swuehl — Sun, 05 Feb 2017 23:22:37 GMT

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.

Re: Generate Dates with specific intervals within a range

umashankarus — Mon, 06 Feb 2017 00:32:53 GMT

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

Re: Generate Dates with specific intervals within a range

swuehl — Mon, 06 Feb 2017 08:13:55 GMT

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

Re: Generate Dates with specific intervals within a range

wh1009 — Tue, 17 Sep 2019 02:24:18 GMT

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.