topic Re: Date Modifiction in the script in QlikView

Date Modifiction in the script

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

Dear ,

I need to put condition as following :

If(isnull([Field]),endofMonth(Month(PeriodDate),-1), PeriodDate) as PeriodDate

Example:

If the Field is null and my periodDate='43160' I need to replace it by '43159'

hope you can help.

Thank you,

Re: Date Modifiction in the script

sunny_talwar — Tue, 23 Jan 2018 09:56:32 GMT

May be this based on required condition

Date(If(Len(Trim(Field)) = 0 and PeriodDate = 43160, PeriodDate - 1, PeriodDate)) as PeriodDate

Re: Date Modifiction in the script

sasikanth — Tue, 23 Jan 2018 10:03:58 GMT

HI,

Use function MonthEnd,Addmonths

If(isnull([Field]),MonthEnd(AddMonths(PeriodDate),-1), PeriodDate) as PeriodDate

Re: Date Modifiction in the script

shiveshsingh — Tue, 23 Jan 2018 10:12:56 GMT

Date(if(len(Trim(Field))=0,Monthend(Addmonths(PeriodDate),-1),PeriodDate)) as PeriodDate

Re: Date Modifiction in the script

sunny_talwar — Tue, 23 Jan 2018 12:10:30 GMT

sasikanth_narne‌ & shiveshsingh‌ -

If you don't mind me asking, why do you need AddMonths here? You are not even giving any value for second paramater and MonthEnd() is handling going back one period... In fact, it would be advisable to add Floor() because MonthEnd() will give you a timestamp. So, if this is what the requirement is, I would use this

Date(If(Len(Trim(Field)) = 0, Floor(MonthEnd(PeriodDate, -1)), PeriodDate)) as PeriodDate

Re: Date Modifiction in the script

shiveshsingh — Tue, 23 Jan 2018 12:19:06 GMT

As per requirement(to counter this endofMonth(Month(PeriodDate),-1),, i put monthend before (Addmonths(PeriodDate),-1).

Monthend will not give timestamp, i guess.. or i missed this part.. I need to check.

Thanks

Re: Date Modifiction in the script

sunny_talwar — Tue, 23 Jan 2018 12:22:08 GMT

MonthEnd, YearEnd, WeekEnd functions always give a timestamp to the last second of the end date

So, MonthEnd(Today()) will give this

01/31/2018 23:59:59

Re: Date Modifiction in the script

shiveshsingh — Tue, 23 Jan 2018 12:28:44 GMT

Do i need to do some setting for timestamp, cz when i tried this in textbox (=Monthend(Today()) )

i got 1/31/2018

Re: Date Modifiction in the script

sunny_talwar — Tue, 23 Jan 2018 12:32:32 GMT

It displays date, but underlying is a TimeStamp. Adding TimeStamp() on top of MonthEnd() will show you that

But if you add floor, you will remove the time (as you would already know this)

Re: Date Modifiction in the script

shiveshsingh — Tue, 23 Jan 2018 13:04:44 GMT

Got it Sunny

Thanks