Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gayatri7
Creator II
Creator 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
sunny_talwar

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
Anonymous
Not applicable

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;

sunny_talwar

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

sunny_talwar

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;

gayatri7
Creator II
Creator II
Author

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.

Anonymous
Not applicable

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;

sunny_talwar

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
Creator II
Creator II
Author

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