Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
PFA
thanks mayil i will check please give me some time and i will get back to u
thanks
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.
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
hi Jagan,
the inline table u mentioned is not my date field please check the sample data i uploaded
thanks
Hi,
Anyway those are the date limits so you can use that. No problem in having that Inline table.
Regards,
Jagan.
ok i will try
from above code what i am doing wrong?
thanks
thanks all
thank you Mayil. can you explain the code what u did please