TM1 Forum

Hi All,

I need to copy and do calculation for budget data. The problem is that budget information is stored on 1 day in month (daily time dimension). Example of hierarchy below:
Sep 14
2014-09-01 (data is stored in this element copied in rules from monthly cube)
2014-09-02 (all other elements in Sep 14 consolidation are no values)

I need to copy data and do some calculations to ProRataBudget scenario example:

NewValue=Value for 2014-09-01 div by working days for whole month.

I need to put this information for all elements I'm choosing in parameters ex.

sFrom=2014-09-01
sTo=2014-09-10

After operation in ProRataBudget scenario I should see the same value for each day from 2014-09-01 to 2014-09-10

I wrote the process for this but this only copy and calculate data for sFrom parameter (in this element when full month number for whole month is stored in rest after process running I see 0)

Any idea how to do this????

Many thanks for any suggestion in advance

My code I've used below:

Parameters: pFrom, PTo

Prolog:

#****Begin: Generated Statements***
#****End: Generated Statements****

sFrom = '' |pFrom| '';
sTo = '' |pTo| '';

## BUILD A VIEW FOR READING

## SET VARIABLES
sCubeName = 'Source Cube';
sViewName = GETPROCESSNAME();
sSubsetName = sViewName;

DatasourceCubeview=sViewName;

## CREATE THE VIEW
IF(VIEWEXISTS(sCubeName,sViewName)=1);
VIEWDESTROY(sCubeName,sViewName);
ENDIF;
VIEWCREATE(sCubeName,sViewName);

## APPLY SUBSETS TO THE VIEW

#################################################################
## SCENARIO DIMENSION
sDimName = 'Scenario';
sInsertElement_1 = 'Source Budget';
IF(SUBSETEXISTS(sDimName,sSubsetName)=1);
SUBSETDELETEALLELEMENTS(sDimName,sSubsetName);
ELSE;
SUBSETCREATE(sDimName,sSubsetName);
ENDIF;
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_1,1);
VIEWSUBSETASSIGN(sCubeName,sViewName,sDimName,sSubsetName);
#################################################################

#################################################################
##TIME DIMENSION
sDimName = 'Time - Daily';
IF(SUBSETEXISTS(sDimName,sSubsetName)=1);
SUBSETDELETEALLELEMENTS(sDimName,sSubsetName);
ELSE;
SUBSETCREATE(sDimName,sSubsetName);
ENDIF;


## GET THE INDEX FROM THE FROM AND TO ELEMENTS
sAttrName = 'Index';
nFromIndex = ATTRN(sDimName,sFrom,sAttrName);
nToIndex = ATTRN(sDimName,sTo,sAttrName);


## BUILD A LOOP TO GET DATES BETWEEN THE FROM AND TO DATE
nX = 1;
nLimit = DIMSIZ(sDimName);

WHILE(nX<=nLimit);
sElementName = DIMNM(sDimName,nX);
nElementIndex = ATTRN(sDimName,sElementName,sAttrName);

IF(nElementIndex>=nFromIndex & nElementIndex<=nToIndex);
SUBSETELEMENTINSERT(sDimName,sSubsetName,sElementName,1);
ENDIF;
nX = nX + 1;
END;
VIEWSUBSETASSIGN(sCubeName,sViewName,sDimName,sSubsetName);
#################################################################

#################################################################
## PRODUCT DIMENSION
sDimName = 'Product';
sInsertElement_1 = '1';
sInsertElement_2 = '2';
sInsertElement_3 = '3';
sInsertElement_4 = '4';
sInsertElement_5 = '5';
sInsertElement_6 = '6';
IF(SUBSETEXISTS(sDimName,sSubsetName)=1);
SUBSETDELETEALLELEMENTS(sDimName,sSubsetName);
ELSE;
SUBSETCREATE(sDimName,sSubsetName);
ENDIF;
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_1,1);
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_2,1);
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_3,1);
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_4,1);
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_5,1);
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_6,1);
VIEWSUBSETASSIGN(sCubeName,sViewName,sDimName,sSubsetName);
#################################################################

#################################################################
## MEASURE REPORTING SALES DIMENSION
sDimName = 'Measure Reporting Sales';
sInsertElement_1 = 'Value';
IF(SUBSETEXISTS(sDimName,sSubsetName)=1);
SUBSETDELETEALLELEMENTS(sDimName,sSubsetName);
ELSE;
SUBSETCREATE(sDimName,sSubsetName);
ENDIF;
SUBSETELEMENTINSERT(sDimName,sSubsetName,sInsertElement_1,1);
VIEWSUBSETASSIGN(sCubeName,sViewName,sDimName,sSubsetName);
#################################################################

## SET VIEW PARAMETERS
VIEWEXTRACTSKIPCALCSSET(sCubeName,sViewName,1);
VIEWEXTRACTSKIPRULEVALUESSET(sCubeName,sViewName,0);
VIEWEXTRACTSKIPZEROESSET(sCubeName,sViewName,1);

Metadata (Empty)

Data
vNewScenario='ProRataBudget';
vParent=ELPAR('Time - Daily',sFrom,1);


## GET WORKING DAYS FOR WHOLE MONTH
sCubeName='Lookup Time - Monthly';
sElementName=vParent;
sWorkingDaysMonth=CELLGETN(sCubeName,sElementName,'Working Days');

## GET WORKING DAYS FOR DAY
sCubeName='Lookup Time - Daily';
sElementName=vTime;
sWorkingDaysForDay=CELLGETN(sCubeName,sElementName,'Working Days');

NewValue=(vAmount\sWorkingDaysMonth) * sWorkingDaysForDay;

IF(CELLISUPDATEABLE('SourceCube',v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13)=1);
CellPutN(NEWVALUE,'SourceCube',v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13);
ENDIF;

EPILOG: (TIDY UP SECTION)

Sorry posted something before but re-read your code and realised I'd missed a step.


You start of with no numbers between days 2 and 10. You add these to your source view but you suppress zeroes in your source view. As such they never get touched.

It seems from your code example that you have a decent enough idea of what you're doing. Looks like as Declan suggested that you aren't seeing any data because the source only has data on day 1 of each month so as the source is zero suppressed you have no data to process. You need to process only 1st of the month and then on the data tab have a while loop to step through the days of the month and spread out the "pro rata budget" to the other days.

From a design perspective I'm not sure of the point to loading the data to the 1st of each month. It could just as well be in a monthly cube and process directly from the monthly cube and pro-rata to the days from there.

Hi Guys,

Thanks for advice will use it and change the process.

Kind Regards

Rafal