Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mwallman
Contributor

How to Generate random dates between a Start Date and End Date variables?

Hello all,

I have two variables where users can input a start date and end date.

Let's called it:

vStartDate

vEndDate

Then I may have a table like this:

Payments:

LOAD

PaymentID,

<Create payment date based on vStartDatr and vEndDate here> PaymentDate,

CustomerID,

ProductID

AutoGenerate 300;

How can I get the PaymentDate to have a date randomly generated from the vStartDate and vEndDate?

17 Replies

Re: How to Generate random dates between a Start Date and End Date variables?

May be like this:

Date($(vStartDate) + Ceil(Rand() * ($(vEndDate) - $(vStartDate)))) as PaymentDate

MVP
MVP

Re: How to Generate random dates between a Start Date and End Date variables?

To get an equal chance to create dates in your range, try something like

Date($(vStartDate)+ Round(Rand() * ($(vEndDate) - $(vStartDate)+1)-0.5)) as PaymentDate

and take care that your variables expand to a numeric representation.

sfatoux72
Valued Contributor

Re: How to Generate random dates between a Start Date and End Date variables?

Hi Swuehl,

It's a good thing to get the same chance for extremity date (vStartDate, vEndDate) than for others dates to be selected. But you will have a problem if Rand() return 1, you will have vEndDate+1 returned...

You need to modify the expression like that:

Date($(vStartDate)+ Round(Rand() * ($(vEndDate) - $(vStartDate)+0.999)-0.5)) as PaymentDate

MVP
MVP

Re: How to Generate random dates between a Start Date and End Date variables?

Actually, I don't really know if the RAND() function returns values in the range [0,1] or ]0,1[ or a half - open variant.

HIC once mentioned [0,1] in one of his blog post but the HELP just states


The function returns a random number between 0 and 1

Anyone ever seen an integer 1 returned ?

Re: How to Generate random dates between a Start Date and End Date variables?

On a AutoGenerate of 100M, the max Rand() * 10000000000 I got was 9999999986.0302

Table:

LOAD Rand() * 10000000000 as Num

AutoGenerate 100000000;

So I guess Stefan might be right.

MVP
MVP

Re: How to Generate random dates between a Start Date and End Date variables?

I tend to trust HIC, but [0,1] strikes me as very strange. I would expect [0,1) as we get with Excel and Java.

MVP
MVP

Re: How to Generate random dates between a Start Date and End Date variables?

Here's a possible argument that it's likely [0,1). We have 52 bits to play with in IEEE 754 double-precision binary floating point, and it would be very rational to interpret that as a number between 0.00000... and 0.11111... binary, which would be [0,1). You could go [0,1], but you'd have to lose one of those random bits to do so, so you'd have only half as many random numbers.

MVP
MVP

Re: How to Generate random dates between a Start Date and End Date variables?

You'd have to run FAR more than 100M to have any sort of statistical certainty that 1 is being excluded, and are likely to have to run FAR more than 100M to see if 0 is included. I suppose that's an argument for "it doesn't matter". Your chance of ever seeing exactly 0 or 1, even if they're included, is extremely remote. Well, assuming actual randomness, which isn't the case. Hard to know with pseudo-random numbers. But I hate "this is statistically unlikely to cause you a problem" as an answer.

Re: How to Generate random dates between a Start Date and End Date variables?

I wish had the computing power to go to a level where the test might give more conclusive results. But just based on a 'SINGLE SMALL' sample test I deduced that Stefan is right.

Am I being a good statistician? No.

Am I being a good friend? Hell Ya!

Community Browser