Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

gayatri7
Contributor II

Aggregate the date value on Month level

Hi ,

I have date field , I want to group all the date field on month basis. I am using the below code but still its not working.

LOAD

date(Month,'MMM YYYY') as Month,

     Collection

FROM

[..\Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

Load Month,

Sum(Collection)as Premium

Resident Data

Group by Month;

Attached is the sample files

1 Solution

Accepted Solutions

Re: Aggregate the date value on Month level

Updated sample qvw file attached

Data:

LOAD Date(MonthStart(Month),'MMM YYYY') as Month,

     Collection

FROM [Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD Month,

  Sum(Collection)as Premium

Resident Data

Group by Month;

DROP Table Data;

View solution in original post

7 Replies
laddu_927
Valued Contributor

Re: Aggregate the date value on Month level

Try

Directory;

Data:

LOAD Month(Month) as Month,

  Year(Month) as Year,

     Collection

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

Load Month&'-'&Year as Month_Year,

Sum(Collection)as Premium

Resident Data

Group by Year,Month;

drop table Data;

Re: Aggregate the date value on Month level

Try adding MonthStart() function to your Month field

LOAD

date(MonthStart(Month),'MMM YYYY') as Month,

     Collection

FROM

[..\Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Aggregate the date value on Month level

Updated sample qvw file attached

Data:

LOAD Date(MonthStart(Month),'MMM YYYY') as Month,

     Collection

FROM [Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD Month,

  Sum(Collection)as Premium

Resident Data

Group by Month;

DROP Table Data;

View solution in original post

gayatri7
Contributor II

Re: Aggregate the date value on Month level

Hi,

Attached is the sample file. I have unique policy id which is having one to one relation with date. I want to aggregate the date value with month irrespective of Policy ids.

laddu_927
Valued Contributor

Re: Aggregate the date value on Month level

You dont need that policy ID at all?? then the above script works for you.

Directory;

data:

LOAD Policy,

     Monthstart(Date) as Month,

     Collection_Amount

FROM

[Sample 1.xlsx]

(ooxml, embedded labels, table is recaudacion_042016);

Load Month(Month),sum(Collection_Amount)

Resident data

group by Month;

Re: Aggregate the date value on Month level

May be like this (Make sure you use MonthStart and not MonthsStart)

Data:

LOAD Policy,

  Date(MonthStart([Date]), 'MMM-YYYY') as Month,

     Collection_Amount

FROM

[Sample 1.xlsx]

(ooxml, embedded labels, table is recaudacion_042016);

Final:

LOAD Month,

  Sum(Collection_Amount)as Premium

Resident Data

Group by Month;

DROP Table Data;

gayatri7
Contributor II

Re: Aggregate the date value on Month level

Thank  you so much!!! it was typo..