topic Subtract dates into a column in QlikView

Subtract dates into a column

alec1982 — Mon, 11 Jun 2012 16:56:46 GMT

Hi,

I have two date columns coming from excel file

Starting date and End Date with the format MM/DD/YYYY

I am building a table chart and would like to have a period column

Anybody knows how could I create a a new column to calculate the period between Start and End date and put it in percentage % of completing the period?

In other words, I would like to get the period in days and then get the percentage of completion of the period on today

Thxs,

Subtract dates into a column

chematos — Mon, 11 Jun 2012 19:45:48 GMT

To get the difference of two dates you only need to use EndDate - StartDate.

So you can calculate both range of days and make the division to get he percentage.

If you don´t get it, please, expose your problem with a complete example.

Regards.

Subtract dates into a column

alec1982 — Mon, 11 Jun 2012 19:56:11 GMT

Hi,

Example: Starting Date 07/09/2010

End Date 12/25/2016

I am looking to build a column to show me the period in total days.

and then another colmun to calculate the current date - starting date in total days.

Thanks for your help

Subtract dates into a column

chematos — Mon, 11 Jun 2012 20:34:22 GMT

You're welcome.

Could you tell me if it works for you doing EndDate - StartDate and Today() - StartDate ???

Take care with Today() function, normally, if you set the date format like:

SET DateFormat='MM/DD/YYYY'; today function returns that format so you could do the substract without problems but if you want, you could use Date(Today(),'MM/DD/YYYY') to be sure that is returned the same type data.

Regards

Subtract dates into a column

Anonymous — Mon, 11 Jun 2012 22:28:30 GMT

Subtracting any 2 dates will give you the number of days between. It is probably safest to use the num() function on the dates to avoid possible syntax errors. For example:
num([End Date]) - num([Start Date]) will give you the entire period.
You will probably want to set a variable with the value of today() as follows in your load script:
LET v_Today = today();
Then you can calculate the difference between today and start as follows:
num(v_Today) - num([Start Date])

-Phil