topic Re: how to remove timestamp from monthend function? in QlikView

how to remove timestamp from monthend function?

Tue, 08 Jul 2014 02:43:09 GMT

Hi all,

Please an some one help me out with the below issue??

In my script I have a row as below:

Monthend(Month) as expiry_date,

the expiry date resultant is presently as 31/1/2016 11:59:59 PM but I want to remove the time stamp from the same in the script level or make the timestamp 0,so that my out put comes after reloading as only 31/1/2016.How is this possible?? please can someone help me??

regards,

Sahana

Re: how to remove timestamp from monthend function?

nilo_pontes — Tue, 08 Jul 2014 02:50:33 GMT

Hi Sahana,

Try: Date(Floor(Month),'DD/MM/YYYY') As expiry_date.

Take a look at Primer for QlikView Date fields for more details.

Regards

Re: how to remove timestamp from monthend function?

Anonymous — Tue, 08 Jul 2014 02:57:31 GMT

Hi,

Try below,

Date(Floor(MonthEnd(Month))) as Expiry_Date

Re: how to remove timestamp from monthend function?

SunilChauhan — Tue, 08 Jul 2014 02:58:04 GMT

Date(Monthend(Month)) as expiry_date,

Re: how to remove timestamp from monthend function?

jagan — Tue, 08 Jul 2014 04:04:49 GMT

Hi Sahana,

Try like this

Date(MonthEnd(Month))

Date(MonthEnd(Month), 'M/D/YYYY')

Date(Floor(MonthEnd(Month)))

Regards,

Jagan.

Re: Re: how to remove timestamp from monthend function?

Tue, 08 Jul 2014 04:50:04 GMT

hi thanks a lot

how can I do the same foe today() function in chart level.

I tried this:expiry_date-Date(Floor(today()))) it works ,but when I do

(expiry_date-Date(Floor(today()))))/365 it doesn't work... the time stamp doesn't become 12:00:00am

Please can you help me??

Re: Re: how to remove timestamp from monthend function?

SunilChauhan — Tue, 08 Jul 2014 04:57:37 GMT

Date(expiry_date)-Date(today())

Re: Re: how to remove timestamp from monthend function?

SunilChauhan — Tue, 08 Jul 2014 04:59:32 GMT

or you can try

age(expiry_date,today())

Re: how to remove timestamp from monthend function?

tresB — Tue, 08 Jul 2014 05:00:09 GMT

Today() returns an integer, so the floor() would not be required for it. Rather try like:

Floor(expiry_date) - today()

But again if you divide the returned integer by 365, you might not get an integer. I could not understand the logic of expecting output 12:00:00.

Re: Re: how to remove timestamp from monthend function?

its_anandrjs — Tue, 08 Jul 2014 05:08:14 GMT

Try with

Date(Floor(Month),'DD/MM/YYYY') As expiry_date // If you need the DD/MM/YYY formats

Re: Re: how to remove timestamp from monthend function?

Tue, 08 Jul 2014 05:11:07 GMT

thanks for all your valuable responses.:)

ok... actually the reason behind getting time stamp 12:00:00. is because we are calculating values without considering the time stamps of date fields,but from today() function which is used it wasn't working so the question.

Re: how to remove timestamp from monthend function?

tresB — Tue, 08 Jul 2014 05:26:53 GMT

If your expiry_date has a value like: 31/1/2016 11:59:59 PM you can truncate the fractional part using floor() like:

Load

Floor(expiry_date) as ExpiryDate

Now in the front-end you are having an integer 42400 equivalent to the date 31/1/2016. If you divide this date difference from today() which is equivalent to 41828 it becomes -

(42400-41828)/365

=572/365

=1.56

Now if you use timestamp() on it, you can't expect a 00:00 because of the fractional part of your result. And here it is clear that - that is not because of today(), but because of simple math - division by 365. Hope this helps.