Need help regarding Quarter,Year and Month from Date

[#1]

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

[Clever_Anjos]

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

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

[#3]

Year field is ok now

.but my month filed gets repeatedly

once see the screenshot attached...

wat about quarterfield?

waiting for ur response?

[Clever_Anjos]

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,

[#5]

Year field is ok now

.but my month filed gets repeatedly

once see the screenshot attached...

wat about quarterfield?

waiting for ur response?

[settu_periasamy · Accepted solution]

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

];

[maxgro]

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,

        ];

[pratap6699]

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

Month(Date) as Month,

Year(Date) as Year