Avg No of Transctions
3 posters
Page 1 of 1
Avg No of Transctions
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
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
Re: Avg No of Transctions
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).
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).
Re: Avg No of Transctions
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.
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
Re: Avg No of Transctions
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
SELECT sum(f.x)/max(d.daysInMonth)... from fact f, date d where f.dateKey = d.dateKey
group by d.year, d.month
Re: Avg No of Transctions
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 : 43
Location : Istanbul, Turkey
Re: Avg No of Transctions
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
Re: Avg No of Transctions
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.
A more general solution would be to count the # of days in a period in a subquery and use that set in your aggregation.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum