List Of Months in current finantial year

[#1]

Hello ,

     how can i get current financial year months like right now finantail year 2014-2015 is going on and current month is june ,i need to create such a field it should store months Apr May June just three if july comes up it should add july to its list... i need help asap,

thank you

[amilafdo]

Hi Rohit,

Copy & Paste below code to edit script (Better if you can add this to the new tab). change the table name to your table & your table date change as TRANSDATE

  eg: Orderdate as TRANSDATE

load

date(TRANSDATE) as TRANSDATE,
DATE(MonthStart(TRANSDATE),'MMM-YYYY') AS CALANDERLINK,
    YEAR(ADDMONTHS(TRANSDATE,-3)) AS YEAR,
    YEAR(TRANSDATE) AS YEAR1,
NUM( MONTH(ADDMONTHS(TRANSDATE,-3))) AS MONTH1,
    IF(MONTH(TRANSDATE) < 4,YEAR(TRANSDATE)-1  &'/'& (YEAR(TRANSDATE) ),
(YEAR(TRANSDATE) )  &'/'& ( YEAR(TRANSDATE)+1)  ) AS [Financial Year],
IF(MONTH(TRANSDATE) < 4,YEAR(TRANSDATE)-1,YEAR(TRANSDATE) ) AS Year,
MONTH(TRANSDATE) AS Monthday2,
TEXT(MONTH(TRANSDATE)) AS MONTHLINK,
num(month(IF(MONTH(TRANSDATE) < 4,MONTH(TRANSDATE) + 9 ,MONTH(TRANSDATE) -3 ))) AS Monthday1,
Week(TRANSDATE) AS Week,
Day(TRANSDATE ) as Day,
DATE(MonthStart(TRANSDATE),'MMM-YYYY') AS [ROLLING MONTH],
    Weekday(TRANSDATE ) as Weekday
    RESIDENT Table name;  // Rename to your table name

    CALANDER:
LOAD * INLINE [
    MONTHLINK, MONTH, Quarter,Monthday
    Apr, 1, Qtr1,1
    May, 2, Qtr1,2
    Jun, 3, Qtr1,3
    Jul, 4, Qtr2,4
    Aug, 5, Qtr2,5
    Sep, 6, Qtr2,6
    Oct, 7, Qtr3,7
    Nov, 8, Qtr3,8
    Dec, 9, Qtr3,9
    Jan, 10, Qtr4,10
    Feb, 11, Qtr4,11
    Mar, 12, Qtr4,12

];