topic Re: Date conversion..Month Start, Month End Date in QlikView

Date conversion..Month Start, Month End Date

pra_kale — Tue, 20 Sep 2016 16:37:48 GMT

Hi,

I want to schedule the report for a MTD extraction. Means it will extract the data like 1 sep to 19 sep, 1 To 20 Sep and on 1 Oct 2016 it will extract the report for complete month i.e. 1 to 30 sep. and again from 2 nd oct onwards it will extract the report 1 to 2nd oct, 1 to 3 rd oct. and so on.

Same way in another report i want to schedule a report on the basis of Financial Month. Means up to 1 oct. Month will be 6 i.e. September and from 2nd Oct. till 1 Nov Month will become 7.

I have created a below given variables...currently this variables are giving correct dates but not sure whether in next month i.e. in 1st Oct it will holds good.

Please suggest..

To find Month start date....=date(if(Today()-1>=MonthStart(Today()-1),MonthStart(Today()-1)))

To Find MTD date.....=date(if(Today()-1<=MonthEnd(Today()-1),(Today()-1)))

To Find FINMONTH...=if(num(month(Today()))<=3,num(Month(Today()))+9,num(Month(Today()))-3)

Thanks in Advance.

Re: Date conversion..Month Start, Month End Date

sunny_talwar — Tue, 20 Sep 2016 17:10:42 GMT

Since today is 20th, you want 1-20 Sep, right?

Try this for MTD

=Date(If(Day(Today()) = 1, Today() - 1, Today()))

MonthStart could be this:

=Date(MonthStart(If(Day(Today()) = 1, Today() - 1, Today())))

Re: Date conversion..Month Start, Month End Date

pra_kale — Tue, 20 Sep 2016 17:30:56 GMT

Hi,

Thanks sunny..Actually for MTD i want data up to 19th so i have modified the formula

=Date(If(Day(Today()) = 1, Today() - 1, Today())-1). Whether it is right..

Secondly, by keeping same formulas of MTD and Month start can I get Data for 1 sep to 30 sep complete month data on 1 oct. and again from 2nd oct i want data for 1 st oct and on 3rd oct for period 1 to 2nd oct and so on..

Re: Date conversion..Month Start, Month End Date

sunny_talwar — Tue, 20 Sep 2016 17:51:23 GMT

What about 2nd Sept? The date needs to be 1 or 2?

Re: Date conversion..Month Start, Month End Date

pra_kale — Tue, 20 Sep 2016 18:11:50 GMT

On 2nd oct. i will require a data of 1 oct..on 3 Oct I require a data frm 1 to 2 nd oct and so on..and on 1 st Nov i will require a data for 1 to 31 oct..means always up to the Month data.

Re: Date conversion..Month Start, Month End Date

sunny_talwar — Tue, 20 Sep 2016 18:14:28 GMT

Then why not just use Date(Today() - 1) for MTD?

Re: Date conversion..Month Start, Month End Date

pra_kale — Tue, 20 Sep 2016 18:34:21 GMT

You are right Sunny..this will do.

i forgot such simple thing..

just one more thing in one report i am going to use below given formula to calculate MTD data same way but on Financial Month..As below given formula currently giving me month as 6 but on 1 st oct it will give me month as 7. Where as on 1st i will require clmplete data for 6 i.e sep. and from 2 nd onwsrds for 7 month 7 i.e Oct.

To Find FINMONTH...=if(num(month(Today()))<=3,num(Month(Today()))+9,num(Month(Today()))-3)

Re: Date conversion..Month Start, Month End Date

sunny_talwar — Tue, 20 Sep 2016 18:43:24 GMT

May be this:

=If(Month(Today() - 1) <= 3, Month(Today() - 1) + 9, Month(Today() - 1) - 3)

Re: Date conversion..Month Start, Month End Date

pra_kale — Wed, 21 Sep 2016 16:00:45 GMT

Thanks Sunny..It is working.