topic Need help regarding Quarter,Year and Month from Date in QlikView

Need help regarding Quarter,Year and Month from Date

Thu, 06 Aug 2015 15:57:18 GMT

Hi QLikview Users,

I have some doubt regarding Dates

in the edit script i have

Data:

LOAD * INLINE [

Date, Sales

4-Aug-15, 100

6-Aug-15, 1000

3-Aug-15, 1500

1-Aug-15, 2000

6-Jul-15, 3000

3-Jul-15, 400

1-Jul-15, 300

3-Jun-15, 1500

6-Jun-15, 1400

1-Jun-15, 1100

1-May-15, 100

1-Apr-15, 1200

1-Mar-15, 1900

1-Feb-15, 1300

1-Jan-15, 1200

12-Dec-14, 1100

11-Nov-14, 1055.238095

10-Oct-14, 1028.809524

9-Sep-14, 1002.380952

8-Aug-14, 975.952381

7-Jul-14, 949.5238095

6-Jun-14, 923.0952381

5-May-14, 896.6666667

4-Apr-14, 870.2380952

3-Mar-14, 843.8095238

2-Feb-14, 817.3809524

1-Jan-14, 790.952381,

];

i want Year,Month and quarter fieds.

can u pls help me

if iam using dis expressions

Date(Date#(Date,'D-MMM-YY'),'MMM') as Month,

Date(Date#(Date,'D-MMM-YY'),'YYYY') as Year

im getting repeated years i.e.,2014 2014 2014 2014 2015 2015............

and repeated Months

pls help

i want quarer field,q1 q2 q3 q4

Month Field as Jan Feb to Dec

Year Field as 2015 2014

Re: Need help regarding Quarter,Year and Month from Date

Clever_Anjos — Thu, 06 Aug 2015 16:04:10 GMT

Date(monthstart(Date#(Date,'D-MMM-YY')),'MMM') as Month,

YEAR(Date#(Date,'D-MMM-YY')) as Year

Re: Need help regarding Quarter,Year and Month from Date

Thu, 06 Aug 2015 16:31:08 GMT

Year field is ok now

.but my month filed gets repeatedly

once see the screenshot attached...

wat about quarterfield?

waiting for ur response?

Re: Need help regarding Quarter,Year and Month from Date

Clever_Anjos — Thu, 06 Aug 2015 16:34:22 GMT

Try this, please

MONTH(Date#(Date,'D-MMM-YY')) as Month,

YEAR(Date#(Date,'D-MMM-YY')) as Year,

QuarterName(Date#(Date,'D-MMM-YY') as Quarter,

Re: Need help regarding Quarter,Year and Month from Date

Thu, 06 Aug 2015 16:35:40 GMT

Year field is ok now

.but my month filed gets repeatedly

once see the screenshot attached...

wat about quarterfield?

waiting for ur response?

Re: Need help regarding Quarter,Year and Month from Date

settu_periasamy — Thu, 06 Aug 2015 16:42:01 GMT

Hi,

Try this,

Data:

Load *,'Q'&ceil(num(Month)/3) as Quarter;

LOAD *,Month(Date#(Date,'D-MMM-YY')) as Month,

Year(Date#(Date,'D-MMM-YY')) as Year;

LOAD * INLINE [

Date, Sales

4-Aug-15, 100

6-Aug-15, 1000

];

Re: Need help regarding Quarter,Year and Month from Date

maxgro — Thu, 06 Aug 2015 16:52:28 GMT

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

Data:

load

Sales,

'Q' & ceil(Month(Date#(Date,'D-MMM-YY'))/3) as Quarter,

Date#(Date,'D-MMM-YY') as Date,

Month(Date#(Date,'D-MMM-YY')) as Month,

Year(Date#(Date,'DD-MMM-YY')) as Year;

LOAD * INLINE [

Date, Sales

4-Aug-15, 100

6-Aug-15, 1000

3-Aug-15, 1500

1-Aug-15, 2000

6-Jul-15, 3000

3-Jul-15, 400

1-Jul-15, 300

3-Jun-15, 1500

6-Jun-15, 1400

1-Jun-15, 1100

1-May-15, 100

1-Apr-15, 1200

1-Mar-15, 1900

1-Feb-15, 1300

1-Jan-15, 1200

12-Dec-14, 1100

11-Nov-14, 1055.238095

10-Oct-14, 1028.809524

9-Sep-14, 1002.380952

8-Aug-14, 975.952381

7-Jul-14, 949.5238095

6-Jun-14, 923.0952381

5-May-14, 896.6666667

4-Apr-14, 870.2380952

3-Mar-14, 843.8095238

2-Feb-14, 817.3809524

1-Jan-14, 790.952381,

];

Re: Need help regarding Quarter,Year and Month from Date

pratap6699 — Thu, 06 Aug 2015 20:47:28 GMT

'Q' Month(Date(Date,'D-MMM-YYYY')/3) as Quarter,

Month(Date) as Month,

Year(Date) as Year