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

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

mwallman — Thu, 19 May 2016 15:24:01 GMT

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?

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

sunny_talwar — Thu, 19 May 2016 15:27:42 GMT

May be like this:

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

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

swuehl — Thu, 19 May 2016 15:44:53 GMT

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.

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

sfatoux72 — Thu, 19 May 2016 19:52:02 GMT

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

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

swuehl — Thu, 19 May 2016 20:11:13 GMT

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?

sunny_talwar — Thu, 19 May 2016 20:17:50 GMT

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.

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

johnw — Thu, 19 May 2016 20:18:28 GMT

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.

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

johnw — Thu, 19 May 2016 20:26:37 GMT

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.

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

johnw — Thu, 19 May 2016 20:30:52 GMT

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?

sunny_talwar — Thu, 19 May 2016 20:35:03 GMT

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!

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

swuehl — Thu, 19 May 2016 20:45:51 GMT

I was somehow hoping that John would jump in

Maybe we should subtract a small number like Sebastien suggested to be on the safe side, or maybe we could use something like

Date(

RangeMin(

RangeMax(

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

,$(vStartDate)

)

,$(vEndDate)

)

) as PaymentDate

This should be super-safe, shouldn't it?

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

johnw — Thu, 19 May 2016 21:06:01 GMT

I'd personally let the possible 1 slide. I suspect the function returns [0,1). And if I'm wrong, I believe there's a 1/(2^52) chance of getting 1. I can live with that. I like to floor() my rand() since I believe in [0,1), so my variation on the theme would be:

date($(vStartDate)+floor(rand()*($(vEndDate)-$(vStartDate)+1))) as PaymentDate

Say we're May 1 to May 10 of 2016.

0.0 - <0.1 : May 1
0.1 - <0.2 : May 2
...
0.9 - <1.0 : May 10
1 exactly : May 11 (may not be possible, is at least highly improbable)

Looks like an equal chance of each date.

EDIT: 1/(2^51+1) chance based on a guess of how they'd tackle including 1 by eliminating one of the random bits

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

sunny_talwar — Thu, 19 May 2016 21:08:00 GMT

Would using a Ceil without adding the one a not so good option here?

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

UPDATE: Or I should say equal to Floor?

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

johnw — Thu, 19 May 2016 21:14:49 GMT

I believe that makes it virtually impossible to get vStartDate. You're randomly distributed between the other dates, so that much is good.

UPDATE: Using floor() isn't why I add 1. I add 1 because I need to randomly distribute between the right number of dates. May 10 - May 1 = 9 days, but I need 10 days.

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

sunny_talwar — Thu, 19 May 2016 21:16:35 GMT

Alright, I need to go back and test what you just mentioned. To me both looked the same , but I might be mistaken and won't realize the difference until I run both the options.

Thanks

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

johnw — Thu, 19 May 2016 21:18:21 GMT

Let us know. I HATE it when people tell me "that won't work because" without testing it, and I totally just did that to you. Even if I turn out to be right, sorry!

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

sunny_talwar — Thu, 19 May 2016 21:22:11 GMT

I don't HATE it John, gives me an option to try, test and learn a new thing. If would have told me that, Yes its the same, I would have trusted you and would have never went back and tested it at my end. But this give me an opportunity to go back and test and learn it

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

johnw — Thu, 19 May 2016 21:45:34 GMT

I almost did it to someone else a few weeks back. It might have been Stephan. Someone wrote an expression that I "knew" was wrong, and I almost said so without testing it, but then I got smart and tested it first. It worked, and once I saw it work, I understood why it worked. Almost put my foot in my mouth. Learned something instead.

Moral of the story: Testing good! Jumping to conclusions bad!