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

[jonathandienst]

Hi

To convert to month (assuming TD_DOC_DT is a normal QV date (numeric)):

     Month(TD_DOC_DT) As Month,

     'Q' & Ceil(Month(TD_DOC_DT) / 3) As Quarter,

If TD_DOC_DT is a text field, then you can use

     Month(Date#(TD_DOC_DT, 'd-MMM')) As Month,

     'Q' &  Ceil(Month(Date#(TD_DOC_DT, 'd-MMM')) / 3) As Quarter

But I would also convert TD_DOC_DT to a proper date field.

HTH

Jonathan

[arulsettu]

Hi Jonathan,

                    Thanks for replying the above code you mentioned will give normal month and quarter. but what i need is for example

                    jan month means -  it should have 1 to 20 days

                    feb month means -  21 from jan to 20 feb...

please check above

thanks

[arulsettu]

any ideas guys?

[jagan]

Hi,

Try this script

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];

Regards,

Jagan.

[arulsettu]

Hi Jagan,

                Sorry i think i dint explain well. i am attaching my date field. from that date field i need month.

jan month means it should contain 1/jan to 20/jan days

feb month means it should contain 21/jan to 20/feb ....

thanks

[#7]

Hi,

you have posted same requirement earlier also & respective solution looks proper ...

Re: Date and Quarter

Re: common month

[arulsettu]

any suggestions please

[MayilVahanan]

Hi

Try like this

Let vStartDate = Floor(YearStart(Today()));
Let vEndDate = Floor(YearEnd(Today()));

Load *,If(Day(Date) <= 20, Month(Date), If(Month(Date) = 12, Month(Date), Month(AddMonths(Date,1)))) As Month;
LOAD
Date($(vStartDate) + IterNo() - 1) AS Date
AutoGenerate 1
WHILE $(vStartDate) + IterNo() -1 <= $(vEndDate);

[arulsettu]

Thanks Mayil,

                         but its not giving desired output

thanks