Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mwallman
Creator III
Creator III

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
swuehl
MVP
MVP

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?

johnw
Champion III
Champion III

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

sunny_talwar

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?

johnw
Champion III
Champion III

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.

sunny_talwar

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

johnw
Champion III
Champion III

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!

sunny_talwar

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

johnw
Champion III
Champion III

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!