Avg No of Transctions

View previous topic View next topic Go down

Avg No of Transctions

Post  kinsun on Wed Apr 18, 2012 2:20 pm

Dear Experts,

I get the following questions in mind and would like to seek your advices:

Suppose I get a FACT Table like the below and it gets 6 records only
Transaction Date SK, Product SK, Customer SK, Quantity

20120101, 1, 1, 10
20120106, 1, 2, 5
20120112, 1, 3, 4
20120404, 2, 4, 3
20120803, 1, 5, 11
20121231, 2, 6, 15

In our BI tool, if we group the records by transaction year month, we would have
201201, 19
201204, 3
201208, 11
201212, 15

And then if we want to know the average quantity sold per month, then we go into problem.
As in the BI tool, for 201201, it would return 3.

So thanks in Advance

kinsun

Posts : 6
Join date : 2012-03-22

View user profile

Back to top Go down

Re: Avg No of Transctions

Post  ngalemmo on Wed Apr 18, 2012 3:01 pm

Given this data for 1/2012:

20120101, 1, 1, 10
20120106, 1, 2, 5
20120112, 1, 3, 4

If the quantities are 10, 5, & 4, why would the average quantity be 3? Why isn't it 6 1/3?

What BI tool are you using? What is the query being generated?

If you are averaging the aggregate, it would have returned 19, not 3. If you want to use the aggregate, you need to include a transaction count and manually calculate the average (total quantity/transaction count).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Avg No of Transctions

Post  kinsun on Wed Apr 18, 2012 10:38 pm

Sorry that I did not present my thinking well.

Data is as follows:
20120101, 1, 1, 10
20120106, 1, 2, 5
20120112, 1, 3, 4
20120404, 2, 4, 3
20120803, 1, 5, 11
20121231, 2, 6, 15

For 201101, the total quantity is 10 + 5 + 4 = 19. When we want to get an average in the BI tool, it is natural for the tool to produce the result as (19 / (transactions count which is 3)). However what would we really want to have is the total quantity divided by the number of days in that month, that is, 31 in this case.

And when we roll-up the data to year level, the expected result should be (10 + 5 + 4 + 3 + 11 + 15) / (365 days) instead of (10 + 5 + 4 + 3 + 11 + 15) / (6 transactions count)

So might I know the best way to achieve this?

Currently I am using COGNOS BIZ INSIGHT ADVANCED to analyze the data.

kinsun

Posts : 6
Join date : 2012-03-22

View user profile

Back to top Go down

Re: Avg No of Transctions

Post  ngalemmo on Thu Apr 19, 2012 12:07 am

Have an attribute called 'days in month' in your date dimension. Use it in the query to calculate the proper average:

SELECT sum(f.x)/max(d.daysInMonth)... from fact f, date d where f.dateKey = d.dateKey
group by d.year, d.month
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Avg No of Transctions

Post  umutiscan on Thu Apr 19, 2012 4:33 am


Using database functions may be a workaround solution for you.
This can be done in Oracle like below.

SELECT SUM (quantity) / MAX (TO_NUMBER (TO_CHAR (LAST_DAY (transaction_date), 'DD'))),
TO_CHAR (transaction_date, 'YYYYMM')
FROM your_table
GROUP BY TO_CHAR (transaction_date, 'YYYYMM')

SELECT SUM (quantity) / MAX ( TRUNC (ADD_MONTHS (transaction_date, 12), 'YYYY') - TRUNC (transaction_date, 'YYYY')),
TO_CHAR (transaction_date, 'YYYY')
FROM your_table
GROUP BY TO_CHAR (transaction_date, 'YYYY')

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Avg No of Transctions

Post  kinsun on Thu Apr 19, 2012 5:55 am

ngalemmo wrote:Have an attribute called 'days in month' in your date dimension. Use it in the query to calculate the proper average:

SELECT sum(f.x)/max(d.daysInMonth)... from fact f, date d where f.dateKey = d.dateKey
group by d.year, d.month

Thanks! Yet I am thinking if it is going to be grouped by year, would it still work?

kinsun

Posts : 6
Join date : 2012-03-22

View user profile

Back to top Go down

Re: Avg No of Transctions

Post  ngalemmo on Thu Apr 19, 2012 8:29 am

Well, it's not exactly a clean solution. You would need a 'days in year' attribute in the dimension (as well as a quarter and week values). Fortunately there are not that many ways you can break up a calendar, so it is not that complicated.

A more general solution would be to count the # of days in a period in a subquery and use that set in your aggregation.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Avg No of Transctions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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