Changing from monthly/quarterly trends on same chart.

[wgonzalez]

Hi,

I have a chart that shows monthly absenteeism trend.  Now I want the user to be able switch from monthly to quarterly.  I tried by creating a drill-down group dimension, but the quarterly figure is wrong.  In order to calculate quarterly absenteeism % I have to sum two data items per month (aggregate them) first.  I'm having issues on geting this.

Any suggestions?

Thanks.

[Anonymous]

Hi Wilfredo,

Hard to give an exact answer without seeing the document. It does sound like the Aggr() function could help you though allowing you to aggregate over the Month values, even when switching to the quarterly dimension view.

[sujeetsingh]

just post a sample...it will be much helpful./

[#4]

Hi,

U need to create the Quarter field and Monthly field form ur Date field

Month= Month(DateField) as Month

Quarter= 'Q' & Num(Ceil(Num(Month(Datefield))/3)) as Quarter

Then, Add this field in Drildown Group or Cyclic Group.

I hope this Helps U....

Regards

Kabilan K.

[wgonzalez]

I have this Pivot whose dimension is Month.

% = HL / AH

Month	HC	Avail Days	HN	AH	HL	%
Jul	672	22	28,732.8	89,539.3	7,198.5	8.0%
Aug	668	23	5,477.3	117,434.8	9,818.8	8.4%
Sep	668	20	9,138.8	97,741.3	7,139.0	7.3%

To get Quarterly % I need to sum AH of the three months, the same for HL then calculate %.  When I make the pivot with dimension Quarter the wrong % is provided (see below).  The correct quarterly % is 7.9%.

Quarter	HC	Avail Days	HN	AH	HL	%
Q3	2,008	65	43,348.8	1,000,811.3	24,156.3	2.4%

I now I need to calculate AH on each individual month, sum them.  Do the same for HL, then calc %.  The question is how can I aggregate by Month, sum the result to then calculate %.

Thanks.

[#6]

Hi,

Please find this attachment,

I hope this helps u.

Regards,

Kabilan K.

[sujeetsingh]

A good answer by Kabilan ..

Just get Month field as dimention and  then in expression Sum(Al)...and all you want.

[wgonzalez]

Thanks, I'm taking the idea to implement a solution.