topic Re: Creating month in QlikView

Creating month

arulsettu — Wed, 15 Apr 2015 06:39:42 GMT

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

Re: Creating month

jonathandienst — Wed, 15 Apr 2015 06:46:10 GMT

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

Re: Creating month

arulsettu — Wed, 15 Apr 2015 06:52:53 GMT

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

Re: Creating month

arulsettu — Wed, 15 Apr 2015 07:17:32 GMT

any ideas guys?

Re: Creating month

jagan — Wed, 15 Apr 2015 07:21:07 GMT

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.

Re: Creating month

arulsettu — Wed, 15 Apr 2015 07:29:05 GMT

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

Re: Creating month

Wed, 15 Apr 2015 07:33:13 GMT

Hi,

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

Re: Date and Quarter

Re: common month

Re: Creating month

arulsettu — Wed, 15 Apr 2015 07:47:39 GMT

any suggestions please

Re: Creating month

MayilVahanan — Wed, 15 Apr 2015 07:50:42 GMT

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

Re: Creating month

arulsettu — Wed, 15 Apr 2015 08:00:41 GMT

Thanks Mayil,

but its not giving desired output

thanks

Re: Creating month

MayilVahanan — Wed, 15 Apr 2015 08:09:04 GMT

PFA

Re: Creating month

arulsettu — Wed, 15 Apr 2015 08:27:07 GMT

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

thanks

Re: Creating month

jagan — Wed, 15 Apr 2015 08:46:14 GMT

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.

Re: Creating month

arulsettu — Wed, 15 Apr 2015 08:57:42 GMT

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

Re: Creating month

arulsettu — Wed, 15 Apr 2015 08:58:38 GMT

hi Jagan,

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

thanks

Re: Creating month

jagan — Wed, 15 Apr 2015 09:12:39 GMT

Hi,

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

Regards,

Jagan.

Re: Creating month

arulsettu — Wed, 15 Apr 2015 09:14:43 GMT

ok i will try

Re: Creating month

arulsettu — Wed, 15 Apr 2015 09:33:16 GMT

from above code what i am doing wrong?

thanks

Re: Creating month

arulsettu — Wed, 15 Apr 2015 09:59:04 GMT

thanks all

Re: Creating month

arulsettu — Wed, 15 Apr 2015 09:59:48 GMT

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