Rule for RPD

[manu0521]

Hi ,

I have a cube with time dimension that goes from year to calendar day level.I alos have another dimension called business days .If there are some calendar days thats not a business day , we will load it to the prior business day or future business day .

Now users wanted a measure caled Sales RPD which is salesrevenueperday .

Now that it makes sense to show the rpd only at month level , I will haev to write a rule say that , if may 2021 is chosen on month and all business ays are chosen , then ake the sales for that month and divide by number of business days for the month, if its looked at year then I have to look at the sales for the year divied by number of business days for that year .

I am planningto store the numbe rof business days completed as an attribute on time dimension .

Can any one give me an example of how the rule and feeder should be ,

should it be only at C level and have an additional check to see if its year or month level and then perform the calulation .

[RPD]=c: if (!month level is 2 or 3 ) then sales /(Attribute of timeperiod to get business days )



Thanks,

[Steve Rowe]

Hi Manu,

As you are probably aware feeders only take place at the N level in a cube, so if you have a rule that is naturally only a C level and the Ns will be unpopulated you need to find a way of feeding and / or populating at least 1 N level under the C.

So you could extend your rule

[RPD]=N: If ( day is first day of month , [Sales] , 0);

and

[Sales]=>DB ( If (day is first day of month , 'CubeName, '') ,....., 'RPD' ....);

The above might be a little cumbersome so you could skip the conditionality, really depends on the size of your model and how much RAM you have.

Alternatively you could load the first days data to RPD as well as sales on import (Actuals only?).

Alternatively you could build a summary cube where the the time is summarised to month and year and write your rule there with a cross cube feeder. This might make sense if your daily cube is a little slow and you are going to have to do more derived sales measures. i.e. Populate the summary cube with the totals from the daily cube and people use this in general except for when they want to drop down for daily analysis.

Cheers,