Creating month

[arulsettu]

hi i am having date field TD_DOC_DT. i can able to get month from this filed.

but what i need is

1-jan to 20- jan - is jan month

21-jan to 20-feb - is feb month

21-feb to 20-mar - is mar month

21-mar to 20-apr - is apr month

21-apr to 20-may - is may month

21-may to 20-jun - is jun month

21 -jun to 20-jul - is jul month

21-jul to 20-aug - is aug month

21-aug to 20-sep - is sep month

21-sep to 20-oct - is oct month

21-oct to 20-nov - is nov month

21-nov to 31-dec - is dec month

based on the above month i need to create quarter also.

plz suggest something

thanks

[MayilVahanan · Accepted solution]

Hi

PFA

[arulsettu]

thanks mayil i will check please give me some time and i will get back to u

thanks

[jagan]

Hi Arul,

Try this script

LOAD

Month,

Left(Date, Index(Date, '/', 2) - 1) AS Date_Month;

LOAD

*,

Date(StartDate + IterNo() - 1) AS Date

WHILE StartDate + IterNo() -1 <= EndDate;

LOAD

*,

Capitalize(Right(Period, 3)) AS Month,

Date(Date#(SubField(Replace(Period, ' ', ''), 'to', 1) & '-' & Year(Today()), 'DD-MMM-YYYY')) AS StartDate,

Date(Date#(SubField(Replace(Period, ' ', ''), 'to', 2) & '-' & Year(Today()), 'DD-MMM-YYYY')) AS EndDate

INLINE [

Period

1-jan to 20- jan

21-jan to 20-feb

21-feb to 20-mar

21-mar to 20-apr

21-apr to 20-may

21-may to 20-jun

21 -jun to 20-jul

21-jul to 20-aug

21-aug to 20-sep

21-sep to 20-oct

21-oct to 20-nov

21-nov to 31-dec];

LOAD TD_DOC_DT,

Text(Date(TD_DOC_DT, 'M/D'))  AS Date_Month

FROM

[Date.xls]

(biff, embedded labels, table is [Sheet 1$]);

Regards,

Jagan.

[arulsettu]

Hi Mayil,

               Its working perfect.

If(Day(TD_DOC_DT) <= 20, Month(TD_DOC_DT), If(Month(TD_DOC_DT) = 12, Month(TD_DOC_DT), Month(AddMonths(TD_DOC_DT,1)))) As monthnum

but one problem i used the month in YTD like this

=fabs(sum({<ACNT_FLEX_01={'Income'},ACNT_FLEX_03={'1- Gross Premiums'},TD_DRCR_FLAG={'D'},Td_month=,TD_YEAR=,TD_DOC_DT={">=$(=date(YearStart(max(TD_DOC_DT))))<=$(=date(max(TD_DOC_DT)))"}>}TD_AMT_LC_1)-sum({<ACNT_FLEX_01={'Income'},ACNT_FLEX_03={'1- Gross Premiums'},TD_DRCR_FLAG={'C'},Td_month=,TD_YEAR=,TD_DOC_DT={">=$(=date(YearStart(max(TD_DOC_DT))))<=$(=date(max(TD_DOC_DT)))"}>}TD_AMT_LC_1))

if i select feb month it showing result only 21/jan to 20/feb. what i am doing wrong

thanks

[arulsettu]

hi Jagan,

               the inline table u mentioned is not my date field please check the sample data i uploaded

thanks

[jagan]

Hi,

Anyway those are the date limits so you can use that.  No problem in having that Inline table.

Regards,

Jagan.

[arulsettu]

ok i will try

[arulsettu]

from above code what i am doing wrong?

thanks

[arulsettu]

thanks all

[arulsettu]

thank you Mayil. can you explain the code what u did please