how to aggregate months as quarter to get aggregated volume?

[udaya_kumar]

Hi,

i have a field known as 'Month Name',

the data for this looks like, jan 2005, feb 2005, mar 2005 till.... dec 2011

In the pivot table, i will show these values as column, by dragging this field to top.

so it looks like the attached image.

but now i want to show 3months volume as single column in table like

jan 2005, feb 2005, mar 2005 volume will be aggregated and it should show as one column 'quarter1'.

like wise, i have to do for every 3months sum, i need to show as a column in table

how we can achieve this?

[jvitantonio]

You can join  your fact with a calendar table where you have with month belong to which quarter.

[CELAMBARASAN]

Hi,

     Then its better to have a quarter field and use the same as dimension likewise you have used the month now.

     For that create a month field using Month(Datefield) as MonthField.

    QuarterTable:

     Load * Inline

     [Month,Quarter

     1,Q1

     2,Q1

     3,Q1

     4,Q2

     5,Q2

     6,Q2

     7,Q3

     8,Q3

     9,Q3

     10,Q4

     11,Q4

     12,Q4];

Celambarasan

[jagan]

Hi,

Arrive an extra dimension by using

LOAD

     *,

'Q' & Ceil(Month(DateFieldName)/3) AS Quarter

FROM DataSource;

Now use this Quarter as dimension in place of month.

Regards,

Jagan.

[udaya_kumar]

hi celambarasan,

thanks for reply,

can you please explian in detail?

i have field name as MONTH_NAME,

so do i need to use like Month(MONTH_NAME) as MonthField?

here do i need to put Month as MonthField?

QuarterTable:

     Load * Inline

     [MonthField,Quarter

     1,Q1

     2,Q1

     3,Q1

     4,Q2

     5,Q2

     6,Q2

     7,Q3

     8,Q3

     9,Q3

     10,Q4

     11,Q4

     12,Q4];

[udaya_kumar]

Hi jagan,

i tried this, it shows 12 quarters by considering all the 72months in the Month_name field.

but i want to show like quarter1(2005), quarter2(2005),quarter3(2005), quarter4(2005), quarter1(2006),...

like this i have to show, even the data (volume) should be aggregated like this.

[jagan · Accepted solution]

Hi,

Try this

LOAD

     *,

'Quarter' & Ceil(Month(DateFieldName)/3) & '(' & Year(DateFieldName) & ')' AS QuarterYear

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     Then it is simple

     Create a field as Jagan Suggestion with the year like

     'Q' & Ceil(Month(DateFieldName)/3) & '-' & Year(DateFieldName) AS Quarter

Celambarasan

[Anonymous]

LOAD
*,
'Q' & Ceil(Month(DateFieldName)/3) & '-' & Year(DateFieldName) AS Quarter
FROM DataSource;

[udaya_kumar]

Thanks everyone for the help,

it works fine.

thank u all very much