topic Re: create a random date column in QlikView

create a random date column

alec1982 — Fri, 10 Jul 2015 13:32:59 GMT

hi all,

I have a dashboard used for demo purposes. one of the columns on it is a date column (expiration date). the data comes from an excel file and I have to update the dates column every several months to make sure they are in the future.. is there anyway I can create a dynamic column to show random dates within the next year or two years?

Thanks,

Alec

Re: create a random date column

sunny_talwar — Fri, 10 Jul 2015 13:45:20 GMT

May be do this:

Table:

LOAD Date(Today() + Floor(Rand()*365)) as FutureDates

AutoGenerate 50;

Re: create a random date column

awhitfield — Fri, 10 Jul 2015 13:45:36 GMT

Hi Alec,

check out the attached thread, tweaking the start and end as required.

Andy

https://community.qlik.com/thread/104652

Re: create a random date column

sunny_talwar — Fri, 10 Jul 2015 13:47:40 GMT

For one year use 365, for two you can use 730 and so on....

Table:

LOAD Date(Today() + Floor(Rand()*365)) as FutureDates

AutoGenerate 50;

HTH

Best,

Sunny

Re: create a random date column

alexandros17 — Fri, 10 Jul 2015 13:48:43 GMT

Try this expression:

Date(Num(Today()) + 365 - DayNumberOfYear(Today()) + Rand()*732)

Re: create a random date column

Anonymous — Fri, 10 Jul 2015 13:55:34 GMT

tmp_MaxDate:

Load min(OrderDate) as mindate,

max(OrderDate) as maxdate

from excel;

LET vMinDate = Peek('mindate',0,'tmp_MaxDate');

LET vfutureDate = today()+90;

LET vMaxDate = Peek('maxdate',0,'tmp_MaxDate');

drop table tmp_MaxDate;

//To get the calendar from your minimum date to maximum date +90 days

//********************* Temporary Calendar ***********************//

tmp_CALENDAR:

LOAD

Date($(vMinDate) + RowNo() - 1) AS tmp_Date

AUTOGENERATE

$(vMaxDate) + $(vfutureDate ) + 1;

Re: create a random date column

alec1982 — Fri, 10 Jul 2015 14:49:02 GMT

Thank you all!

Re: create a random date column

sunny_talwar — Fri, 10 Jul 2015 14:52:56 GMT

Not a problem, glad we were able to help.

Best,

Sunny