Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
arulsettu
Master III
Master III

Creating month

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

19 Replies
MayilVahanan

Hi

PFA

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
arulsettu
Master III
Master III
Author

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

thanks

jagan
Partner - Champion III
Partner - Champion III

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
Master III
Master III
Author

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
Master III
Master III
Author

hi Jagan,

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

thanks

jagan
Partner - Champion III
Partner - Champion III

Hi,

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

Regards,

Jagan.

arulsettu
Master III
Master III
Author

ok i will try

arulsettu
Master III
Master III
Author

from above code what i am doing wrong?

thanks

arulsettu
Master III
Master III
Author

thanks all

arulsettu
Master III
Master III
Author

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