Hi Friends stuck with Date Functions on Open of Document to show Year,Month,Quarter,Fiscal Month

[#1]

Hi Friends since last couple of days I am stuck with an Issue can any help on this Problem which I’m facing

So this is my Question,I have Data with 2015 February month source has Year,Eff Month columns I need to show in default selected the actual Year and Month. I have used the Triggers on Open and passed Year,month,Quarter but the Acual Issue starts is for 2015(Year),there are Eff Months as below

Expected result: We will get Source data monthly once suppose If March is current Month & we get February data Source from business team.

On open of QVW need to display Current Year=2015, Month= February(02)(Here always need Monthname-1) Based on Month need to show Fiscal Month and Quarter Year ==max(Year) [Eff Month] ==max([Eff Month])

Issue 1:No Use of Using Eff Month as to display Year=2015,Eff Month=2,Quarter=Q1 Any help on Achiving this in my case

Issue 2:How to derive Fiscal Month from Existing Month

Issue 3:Is there any way to Include a Dummy column in Separate Sheet or Use of vLoadDate=now() and to get Year,Month,Quarter,Fiscal month out of It.

Issue 4: How to get Monthname from [LastReload] =201502 (i.e., 02-Feb) LastReload 201502 (Here how to get Month out of 201502 by using Right([LastReload) how to get Month number I am Using vLoadDate=now() [Fiscal Month] =Month(vLoadDate)-1& '/'& Right(Year(vLoadDate),2)

[jagan]

Hi Srikanth,

Try this in triggers

Year = Year(MonthStart(Today(), -1))

[Eff Month]  = Month(MonthStart(Today(), -1)) * 1

Calendar Quarter = Year(MonthStart(Today(), -1)) 'Q' & Ceil(MonthStart(Today(), -1)/3)

Regards,

Jagan.

[#3]

Jagan thanks for your solution but in certain case of my requirement it is not getting desired output.

Suppose I have Recent column values in it is  201502 (yyyymm) need to get year,and also how to get month out of it (Right (Recent,2))please correct me getting month,I need if else logic to have a Year=max(2015),then get latest month.

How to get Fiscal month?

Hope you will help on this.Thanks

[jagan]

Hi,

You can get month like this from 20502

Num(Right('201502', 2))

What is the logic for Fiscal Moth?

Regards,

Jagan.