Skip to Content
0
Former Member
Jun 08, 2009 at 06:57 AM

Query Agreegation issue

24 Views

Hi Experts,

I have defined weekly, monthly , quarterly view on my base report. The report data looks like this.

Quarter Month Week Unit Cost Formula ( Unit * Cost = Total cost)

Q3 APR 1 50 2.0 100.00

Q3 APR 2 50 2.5 125.00

Q3 APR 3 50 2.5 125.00

Q3 APR 4 50 2.5 125.00

-


475.00

So the total cost for monthly view should come out 475.00 but actaully what happens is that units and Cost gets aggregated before formula calculation so total cost comes out as - 200 * 9.5 = 1900.

I have tried using exception aggregation using first or last value by each week change but stil the result is not correct as

200 * 2.0 = 400 OR

200 * 2.5 = 500

What I want is that like the above table the total cost is calculated without aggregation at each week level and the formula output is aggregated at monthly level to get the correct total cost as 475.

Thanks

Vishal