Topic
• 2 replies
• Latest Post - ‏2012-04-23T13:32:14Z by Xwh_CA
IMTKSL
1 Post

# Pinned topic Avg Quantity per Number of Days in a Month

‏2012-04-20T00:58:30Z |
Dear Experts,

Given a simple data model like the below:

Sales Order Fact (Transaction Date SK, Product SK, Quantity)
Product Dimension (Product SK, Product Code, Product Desc)
Date Dimension (Date SK, Date, Year, Year Month, Quarter)

And data:
Transaction Date, Product, Quantity Ordered
2011-01-01, 'Green Tea', 10
2011-01-05, 'Red Tea', 20
2011-02-04, 'Green Tea', 20
2011-02-07, 'Red Tea', 10
2011-04-15, 'Green Tea', 10
2011-04-20, 'Green Tea', 20
2011-04-25, 'Red Tea', 30
2011-08-01, 'Green Tea', 20
2011-12-31, 'Red Tea', 10

With Business Insight Advanced (BIA), when I am going to calculate the average of quantity ordered, it is natural to give the following results by the tool:
2011-01: (10+20) / (1+1)
2011-02: (20+10) / (1+1)
2011-04: (10+20+30) / (1+1+1)
2011-08: (20) / (1)
2011-12: (10) / (1)

However the user requirement is to have the quantity be divided by the number of days in that month, that is as follows:
2011-01: (10+20) / (31)
2011-02: (20+10) / (28)
2011-04: (10+20+30) / (30)
2011-08: (20) / (31)
2011-12: (10) / (31)
And if year then
2011: (10+20+20+10+10+20+30+20+10) / (365)

So might I know how this could be achieved in the BIA?
Updated on 2012-04-23T13:32:14Z at 2012-04-23T13:32:14Z by Xwh_CA
• DanielWagemann
850 Posts

#### Re: Avg Quantity per Number of Days in a Month

‏2012-04-20T17:53:57Z
If your Time dimension has a days in month field, you could create the average and total year average manually as its own column.

So you would have:
Year(Grouped) Year-Month(Grouped) DaysInMonth Product Line Quantity Year-MonthAvgCalc and YearAvgCalc

Year-MonthAvgCalc would be (total(Quantity for Year,Year-Month)/DaysInMonth)

YearAvgCacl would be total(Quantity for Year)/ total (DaysInMonth for Year)
• Xwh_CA
1 Post

#### Re: Avg Quantity per Number of Days in a Month

‏2012-04-23T13:32:14Z
1. Assuming your time table is fully defined from Jan 01 to Dec 31.
2. change the relationship between time and salse from 1:1--1:n to 1:1 --0:n
3. define your avg as total(QTY) / count(rows)