Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
Try this
LOAD
*,
'Quarter' & Ceil(Month(DateFieldName)/3) & '(' & Year(DateFieldName) & ')' AS QuarterYear
FROM DataSource;
Hope this helps you.
Regards,
Jagan.
You can join your fact with a calendar table where you have with month belong to which quarter.
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
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.
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];
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.
Hi,
Try this
LOAD
*,
'Quarter' & Ceil(Month(DateFieldName)/3) & '(' & Year(DateFieldName) & ')' AS QuarterYear
FROM DataSource;
Hope this helps you.
Regards,
Jagan.
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
LOAD
*,
'Q' & Ceil(Month(DateFieldName)/3) & '-' & Year(DateFieldName) AS Quarter
FROM DataSource;
Thanks everyone for the help,
it works fine.
thank u all very much