Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Inconsistent Bi-weekly dates

Hi,

I am trying to calculate Bi-weekly dates, i referred to this discussion Creating Bi Weekly in Master Calendar and used the same calculation i.e dual(text(weekstart(create_date)),div(weekstart(create_date),14)) as "BiWeekdate".

This is giving me inconsistent dates like shown below. Since the interval is 14 days, for example after 27/12/2015 the next bi-weekly date should be 10/01/2016 but instead it shows 17/01/2016.

What could be the reason? Anything missing here? Any help is appreciated.

inconsitent_biweekly_dates.png

Thanks

Swetha

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Inconsistent Bi-weekly dates

The formula is wrong. Try the following instead:

     Date(2+14*Div(create_date-2,14)) as BiweekStart

HIC

6 Replies
ift_isabelle
Contributor II

Re: Inconsistent Bi-weekly dates

Do you have data on 10/01/2016?

If not: Did you uncheck the box where teh visualisation doesn't display Null-values?

Not applicable

Re: Inconsistent Bi-weekly dates

Hi Isabelle, there is data for 10/01/2016 and the Show Null values check box is true.

Thanks

Swetha

Highlighted
Employee
Employee

Re: Inconsistent Bi-weekly dates

The formula is wrong. Try the following instead:

     Date(2+14*Div(create_date-2,14)) as BiweekStart

HIC

Not applicable

Re: Inconsistent Bi-weekly dates

Thanks Henric.. that worked like a charm.

Thanks

Swetha

Re: Inconsistent Bi-weekly dates

Hi,

maybe also possible:

Date(Floor(Date,14,2)) as BiWeekDate

QlikCommunity_Thread_232760_Pic1.JPG

LOAD *,

     WeekStart(Date) as WeekStart,

     Date(Floor(Date,14,2)) as BiWeekDate;

LOAD Date(MakeDate(2015,12,28)+RecNo()-1) as Date

AutoGenerate Today()-MakeDate(2015,12,28)+1;

hope this helps

regards

Marco

Re: Inconsistent Bi-weekly dates

or to let weeks start on sundays, like in your case:

Date(Floor(Date,14,1)) as BiWeekDate

regards

Marco

Community Browser