Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Ledgered fact vs average aggregation

Go down

Ledgered fact vs average aggregation Empty Ledgered fact vs average aggregation

Post  adisutanto 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

Back to top Go down

Ledgered fact vs average aggregation Empty Re: Ledgered fact vs average aggregation

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Ledgered fact vs average aggregation Empty Re: Ledgered fact vs average aggregation

Post  krvin 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

Back to top Go down

Ledgered fact vs average aggregation Empty Re: Ledgered fact vs average aggregation

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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