How can i Aggregate from Date level to Month Level on the Date Field

[madhubabum]

Hi All

I have the following Table with the following fields

Table:

ProfitCenterId,

EquipmentModelName,

%Date (Key Column),

WorkOrderType,

ActualHours

From D:Table.qvd;

Note : I got placed "MASTER CALENDAR" in my Application

I have the Following Date's (MM/DD/YYYY) Format

Date_Field

12/31/2000

11/12/2002

11/22/2002

12/4/2002

5/2/2003

11/6/2006

12/22/2006

11/15/2007

10/22/2008

12/30/2009

10/10/2010

12/19/2010

9/12/2013

10/10/2014

12/30/2014

I want to Aggregate " ActualHours "  in " MONTH " Level ...How can we achieve ?

[jfkinspari]

Do you want to script a new table per Month level, or do you just want to create a table in UI?

[madhubabum]

Hi Jens ,

I want to script a new table per Month level.

[jfkinspari]

Okay, then you need to start my king a month field availbale in the table. To do so you could make a applymap from the MasterCalendar, or you could simple use a function, like firstdayinmonth() to get a date field on month level.

Next make a new load where you group the data

NewTable:

ProfitCenterId,

EquipmentModelName,

****%Date (Key Column)****,

WorkOrderType,

sum(ActualHours) as TotalHours

Resident Table

Group by

ProfitCenterId,

EquipmentModelName,

****%Date (Key Column)****,

WorkOrderType;

Drop Table;

[madhubabum]

HI Jens

But , there is no " firstdayinmonth() " function in our script level

[jfkinspari]

Okay, the precise definition of function is

MonthStart(date [, shift = 0])

[madhubabum]

Hi Jens

But , It will showing an error on  " [, shift = 0] "

May i know what is that meaning ?

[Kushal_Chawda]

try below in youe script

=monthname(Date_Field) as Month