Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];