Monthly Average (Expected)

Hi, I want to create a calculation in the cube (thinking that it can be done on that way). Please help me, you'll save a life :P

Problem:

TICKET NR

CUSTOMER

CITY

MONTH

YEAR

PROBLEM COUNT

IM1234499

BEU

ISTANBUL

1

2006

1

IM1234500

CTB

IZMIR

1

2006

1

IM1234501

BEU

ISTANBUL

2

2006

1

IM1234502

CTB

IZMIR

2

2006

1

IM1234503

BEU

ANKARA

3

2006

1

IM1234504

ASE

ANKARA

3

2006

1

IM1234505

BEU

ISTANBUL

4

2006

1

IM1234506

CTB

IZMIR

4

2006

1

IM1234507

BEU

ANKARA

5

2006

1

IM1234508

ASE

ISTANBUL

5

2006

1

IM1234509

BEU

ISTANBUL

5

2006

1

IM1234510

CTB

IZMIR

6

2006

1

IM1234511

BEU

ANKARA

6

2006

1

IM1234512

ASE

ADANA

6

2006

1

IM1234513

BEU

ISTANBUL

6

2006

1

IM1234514

CTB

IZMIR

7

2006

1

IM1234515

BEU

ANKARA

7

2006

1

IM1234516

ASE

ANKARA

7

2006

1

IM1234517

BEU

ANKARA

8

2006

1

IM1234518

ASE

ANKARA

9

2006

1

IM1234519

BEU

ANKARA

9

2006

1

IM1234520

ASE

ANKARA

9

2006

1

IM1234521

BEU

ANKARA

9

2006

1

IM1234522

BEU

ANKARA

10

2006

1

IM1234523

BEU

ANKARA

11

2006

1

IM1234524

BEU

IZMIR

12

2006

1

IM1234525

ASE

ISTANBUL

1

2007

1

IM1234526

ASE

IZMIR

1

2007

1

IM1234527

BEU

ISTANBUL

2

2007

1

IM1234528

ASE

IZMIR

2

2007

1

The table above lists some ticket records in my db. I have two measure;

* Total Count of Ticket
* Average Count of Ticket

First one works fine as expected...

Total PROBLEM COUNT

YEAR

MONTH

2006

Total 2006

2007

Total 2007

Total

CUSTOMER

1

2

3

4

5

6

7

8

9

10

11

12

1

2

ASE

1

1

1

1

2

6

2

1

3

9

BEU

1

1

1

1

2

2

1

1

2

1

1

1

15

1

1

16

CTB

1

1

1

1

1

5

5

Total

2

2

2

2

3

4

3

1

4

1

1

1

26

2

2

4

30

Here is the pivot view yearly and monthly. The total results are produced by Total Count of Ticket.
My second measure's result is as I waited but not my expected one as you see below.

2006

TRANS_MONTH

TOTAL PROB

MONTH AVG OF YEAR

MY EXPECTED AVERAGE

ASE

5

6

1,2

0,5

BEU

12

15

1,25

1,25

CTB

5

5

1

0,4

2007

TRANS_MONTH

TOTAL PROB

MONTH AVG OF YEAR

MY EXPECTED AVERAGE

ASE

2

3

1,5

1,5

BEU

1

1

1

0,5

CTB

0

0

0

0

I think you've got my problem ;-) The average provided by my measure is
total ticket count / count of transaction month

but I want to calculate the average with some other measurement;

If the year ended (past year)
Monthly Average is calculated by total ticket count / 12

If the year not ended (current year)
Monthly Average is calculated by total ticket count / number of last month

Let me show over the tables...

ASE has 6 tickets for 5 months in 2006 and
my expected monthly average of 2006 for ASE is 6/12=0,5 (divided by 12 = end of 2006)

BEU has 1 tickets for 1 months in 2007 (my last recorded month on db is Feb 2007 also) and
my expected monthly average of 2007 for BEU is 1/2=0,5 (divided by 2 = Feb)

Well, I think there is a super-dev would explain how to do it with a calculation (calculated member, right?)
Not good at MDX but waiting for your solution.

Thanks.

I've built a very small solution based on your data.

It consists of 1 fact table and 4 degenerate dimensions (Ticket,Customer,City,Month).

I've created 2 calculated member: the first one count months in the year, the second calculate the average based on the first one

It works fine (the average returns the data you expect) but it could be not exactly what you want (your real business scenario should be more complex!).

If you contact me via e-mail I can send you the SQL db (actually only 1 table!!!) and the AS solution.

francesco.dechirico@.fastwebnet.it(donotspam!)

|||thanks it works fine
sql