Ledgered fact vs average aggregation

View previous topic View next topic Go down

Ledgered fact vs average aggregation

Post  adisutanto on Fri Oct 05, 2012 3:47 am

Suppose we have the fact table below:
Item,Sales
A,3
A,5
B,3
B,5
B,1
B,-1

Notice that item "B" has ledgered entries. The "B,-1" record is the soft delete of "B,1".

Now we want to calculate the average sales of items. Expected result:
Item,Average(Sales)
A,4
B,4

But the actual result is:
Item,Average(Sales)
A,4
B,2

How to properly calculate average aggregation for ledgered fact?

adisutanto

Posts : 1
Join date : 2012-10-05

View user profile

Back to top Go down

Re: Ledgered fact vs average aggregation

Post  ngalemmo on Fri Oct 05, 2012 4:55 am

You don't have enough information. The question is: average what? Average for a day? Average for a transaction? etc...

It winds up being a two pass query. Assuming you have other dimensions, you need to net things out along the dimension you want the average of, then calculate the average of that set.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Ledgered fact vs average aggregation

Post  krvin on Mon Oct 15, 2012 8:28 am

Below SQL code should help you.

with cte AS
(
select item, abs(sales) sales from #temp
group by item, abs(sales)
having count(abs(sales)) > 1

)
select * from #temp
left outer join cte
on cte.item = #temp.item
and cte.sales = abs(#temp.sales)
where cte.item is null

krvin

Posts : 1
Join date : 2012-05-03

View user profile

Back to top Go down

Re: Ledgered fact vs average aggregation

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum