TM1 Forum

Posted: **Wed Jan 27, 2016 5:51 pm**

Hi everyone,

I've been reading through everything I can find on ConsolidatedAvg in the forum and have tried all sorts of different things and I just can't seem to get this to work how I need so I am breaking down and submitting a question to the forum

I have tried changing the order of my dimensions, I have tried different order of my rule, I've tried adding "ConsolidateChildren('WeekDayWeekly')\ConsolidatedCountUnique(0,'WeekDayWeekly'...." to the rule below I have in purple, etc. but I just can't seem to get this to work

I have illustrated below what is currently happening with my rule and then how I need it. I highlighted the rule showing what portion is affecting what part of the cube. Thus, I believe I have to do something to the Purple shaded portion? Or, am I just way off base?

Any help would be greatly appreciated! Thanks a lot!

: ConsolidatedAvg.jpg (153.01 KiB) Viewed 3004 times

Actual code vs screenshot:

Code: Select all

['Inventory'] =C:
	IF(DTYPE('WeekDayWeekly',!WeekDayWeekly) @= 'N' &
        DTYPE('Products',!Products) @= 'C',
	DB('Reporting Actual And Forecast - SKU', !Version, !Actual and Forecast Types, !Products, !WeekDayWeekly, !Actual vs Forecast Measures),
	CONTINUE);

['Inventory'] =C:
	IF(DTYPE('WeekDayWeekly',!WeekDayWeekly) @= 'C' &
	   DTYPE('Products',!Products) @= 'N',
	ConsolidatedAvg(2,'Reporting Actual and Forecast - SKU Weekly', !Version, !Actual and Forecast Types, !Products, !WeekDayWeekly, !Actual vs Forecast Measures),
	CONTINUE);

['Inventory'] =C:
	IF(DTYPE('WeekDayWeekly',!WeekDayWeekly) @= 'C' &
	   DTYPE('Products',!Products) @= 'C',
	ConsolidatedAvg(2,'Reporting Actual and Forecast - SKU Weekly', !Version, !Actual and Forecast Types, !Products, !WeekDayWeekly, !Actual vs Forecast Measures),
	CONTINUE);

Posted: **Wed Jan 27, 2016 7:00 pm**

By george, I think I may have gotten it!! This appears to be working!

I changed my last section (the section in purple) to the following:

Code: Select all

['Inventory'] =C:
	IF(DTYPE('Products',!Products) @= 'C' &
	   DTYPE('WeekDayWeekly',!WeekDayWeekly) @= 'C',
	ConsolidateChildren('WeekDayWeekly') \
	ConsolidatedCountUnique(0,'WeekDayWeekly','Reporting Actual and Forecast - SKU Weekly', !Version, !Actual and Forecast Types, !Products, !WeekDayWeekly,  !Actual vs Forecast Measures),
	CONTINUE);

Does this look okay?

Posted: **Wed Jan 27, 2016 9:48 pm**

Why not consolidate 1 up the tree then set the average to be the total divided by the count?

TM1 Forum

ConsolidatedAvg

ConsolidatedAvg

Re: ConsolidatedAvg

Re: ConsolidatedAvg