Cube Design ? RPD Calculation.

Post Reply
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Cube Design ? RPD Calculation.

Post by manu0521 » Wed May 26, 2021 12:25 am

Hi Tm1 Users,
which
I have the below sales cube with following dimensions.
TImeperiod - Year -> Month->Date
Customer
Products
Sales Measures
Business Day ( Has days from 1 to 23 )

I am having a time period dimension which can track sales by calendar days , business requested to have a business day dimension which will have values from 1 to 23 .

if Apr -1 - Business day1
Apr - 2 - Business day 1
Apr - 3 - Business day 1
Apr -4 - Business day 2

If April 2 and April 3 are non business days their sales are still loaded to the prior business day .


so now they can see the sales by business day as well by picking a month on time period dimension .


Everything works so far for sales ,qty , cost etc...Sales on non business days goes to its prior business day .


Now they wanted to have a measure called RPD - Revenue Per day which is based on business day .

I dont know how to deal with this since I have a calendar day time period dimension .Also the calculation for RPD should be soemthing like

on business day 1 - Same as sales value
on business day 2 - Business day 1 sales + business day 2 sales / 2

now to perform this calculation on the fly and also load to only one calendar day when a business day can have more than 1 calendar day things are getting very complicated.

Any suggestions on how to proceed is highly appreciated.

Post Reply