Tm1 Complex Rule

[Analytics123]

Hi ,

I have a Account Receivable cube (AR Cube)which has customers and balances by month .
I also have a sales cube which has customers with sales data by months .

I need to calculate the last 3 month sales and last 6 month sales in AR cube using a rule from sales cube based on the value selected from the months in AR cube .

Say if user views April 2017 data in AR cube I need to go to sales cube and for the customer get the last three month sales which would be MAR , Feb and Jan 2017 .

As of now I have an attributes in my month element as prior month and I use
[3Month Sales] = DB(Prior month,sales ,customer) + DB((Prior month(Prior Month),sales ,customer) +DB((Prior month(Prior Month(Prior Month)),sales ,customer) .

since the 3 months we pick might change based on user selection of month in AR cube , i cannot create a dynamic 3 months subset for months .

I need to do the same for 6 months as well .

Is there a better approach or should I load back Sales data as well in to AR cube , which will be an overhead if there is any changes in sales data then we have to reload both .

Thanks,

[gtonkin]

First observation is that you are going to run into issues over year ends. Second observation is that if you say "balances" then what comes to mind is that these are as at values, i.e. non-cumulative.
Based on your simplified rule, it appears that you have no years-is your dimension a year/month combination?

[Steve Rowe]

I'd be looking to do the summation in a consolidation as this is the most efficient place to do this, where practical (i.e. you have 1d time).

You'll obviously need many of these consolidations. You can call them something like "Apr 2017 Prior 3 Months"

Then your rule is

[Prior 3 Months]=N: DB ( Sales , !Month | ' Prior 3 Months' , .....);

You might want to build the next 3 and next 6 month consolidations as well so that you can feed from the sales cube.
(Never tested if feeding a 6 month consolidation is faster than feeding from 6 nested Attr (month , !month , 'next') type statements).

You may have something else that you can feed with, depends on what the rest of your data looks like, you might just get away with feeding with the current month but take great care with that approach.

Build these consolidations programmatically and hide them from the user via security.

HTH