topic Re: Aggregate the date value on Month level in QlikView

Aggregate the date value on Month level

gayatri7 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregate the date value on Month level

Anonymous — Tue, 04 Apr 2017 13:18:26 GMT

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

sunny_talwar — Tue, 04 Apr 2017 13:19:01 GMT

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

sunny_talwar — Tue, 04 Apr 2017 13:20:17 GMT

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;

Re: Aggregate the date value on Month level

gayatri7 — Tue, 04 Apr 2017 13:58:54 GMT

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.

Re: Aggregate the date value on Month level

Anonymous — Tue, 04 Apr 2017 14:09:17 GMT

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

sunny_talwar — Tue, 04 Apr 2017 14:18:03 GMT

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;

Re: Aggregate the date value on Month level

gayatri7 — Wed, 05 Apr 2017 06:33:25 GMT

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