Average by month

[J.Donkers]

Dear all,

I just started working with Cognos Express Architect and have the following question.
I build a cube with 3 dimensions; Period - Administration and Persons
The Person dimension is the measure dimension and has the following elements:
Total_Number_Employee, Hours_available, Count_Total_Employee, Average_Employee

By day/month/quarter I want to have the average of the amount of Employees.
I use the following rule to put a temporary field on 1:
['Count_Total_Employee'] = N: IF (['Total_Number_Employee']<> 0,1,0);
['Average_Employee'] =C:['Total_Number_Employee']\['Count_Total_Employee'];

I seems the Count_Ttotal_Employee is increase the value with the elements in the Administration dimension.

Simply I want to haven the following result
Value Total_number_Employee on 01/01/2012 is 100
Value Total_number_Employee on 02/01/2012 is 100
Value Total_number_Employee on 03/01/2012 is 100
Value Total_number_Employee on 04/01/2012 is 100
Value Total_number_Employee on 05/01/2012 is 100
Value Total_number_Employee on 06/01/2012 is 100 till
Value Total_number_Employee on 31/01/2012 is 100
Consolidated January Total_number_Emlpoyee is 3100

Value Average_Employee on 01/01/2012 is 100
Value Average_Employee on 03/01/2012 is 100
Value Average_Employee on 03/01/2012 is 100
Value Average_Employee on 04/01/2012 is 100
Value Average_Employee on 05/01/2012 is 100
Value Average_Employee on 06/01/2012 is 100 till
Value Average_Employee on 31/01/2012 is 100

Consolidated January Average_Employee should be 100 !!!!

Quit simple perheps and hopefully someone can help me!

[declanr]

Put an extra measure into the cube called "Count".

['Count']=N: IF ( ['Value'] > 0, 1, 0);

['Average']= ['Value'] \ ['Count'];


There are numerous variations of the above but that should give you an idea, obviously you will need to substitute ['Value'] and ['Average'] for whatever your measures are called.


I noted however that it doesn't look like you have a measure dimension in your cube, if you don't it would be worthwhile adding one in.

HTH

[J.Donkers]

Dear Declanr,

Unfortunally this doesn't work. In my example Count_Total_Employee is the extra measure field. I expect a value of 1 in this field but It seems I get the count of the total of administrations. Changing this in the outline will change the value of the filed Count_Total_Employee.

Regards,
Johan

[declanr]

Johan,

Can you provide a screenshot of your cube to further explain the issue.

Also its generally easier for the purpose of following the issue if you post subsequent changes in a new comment in the thread rather than changing the original post based on replies to it.

Regards,

Declan

[J.Donkers]

Dear,

Hereby the example. The cube is in Dutch.
But the explanation is as followed
Aantal DTF = Total_number_employee
Berekend_aantal_DTF = Average_Employee
Aantal_DTF_Count = Count_Total_Employee

I expected 1 in the Count_Total_Employee on the 01/01, 02/01 and 03/01 and the value 3 on januari.

Your remark about posting I do not understand, I did not change the original post, I just posted a reply.

[declanr]

Your remark about posting I do not understand, I did not change the original post, I just posted a reply.

Sorry I thought extra information was added to the original post, I apologise since that was not the case.

I am not quite sure what the issue is without being able to see what is in the administration dimension but could it be solved by instead having something along the lines of adding an If statement into the ['Count'] calculation that filters based on an Ellev function?

[J.Donkers]

Hello,

To get the result as I wanted I created the rule shown in the attachtment. I presume there should be a much easier way to get this result.

Another question, I just want to put a value of 1 (numeric) in the D0229 field if there is a result on that day in the measure Aantal_DTF

Thanks in advance.
Johan

[tomok]

To get the result as I wanted I created the rule shown in the attachtment. I presume there should be a much easier way to get this result.

Absolutely. There is no need to write a rule for each leaf level element in the Periode_dag dimension. This would have sufficed:

['Aantal_DTF_Count'] = N:1;

Another question, I just want to put a value of 1 (numeric) in the D0229 field if there is a result on that day in the measure Aantal_DTF

Assuming you want all the other days to still have a value of 1 I would have the following to statements, in this order:

['D0229','Aantal_DTF_Count'] = N:IF(DB('rulestest',!Dim1,!Dim2,!Dim3,!Dim4,'Aantal_DTF')<>0,1,0);
['Aantal_DTF_Count'] = N:1;

[J.Donkers]

Dear Tomok,

Thanks for youre reply.
Unfurtunally your solution is not given me the result I wanted. I get the same results as shown in de previous screenshot_cube/pdf.
In my example I expect a value of 1 in the field but It seems I get the count of the total of administrations. Changing this in the outline will change the value.
To get the average (berekend_aantal_DTF) by month correctly the Aantal_DTF_Count by day should always be 1 and the Aantal_DTF_count by Month should always be the count of those days, no matter how you change the view/dimensions.

As I told before I'm new with TM1, so perhaps I understand this not correctly.

Regards,
Johan

[Venki@1688]

Hi,

If you can create two more elements in the Person Dimension like Total_Number_Employee_Count(Already exists i guess) and Total_Number_Employee_Hold

and in Rules

['Total_Number_Employee_Hold'] = N: ['Total_Number_Emploee'];
['Total_Number_Employee_Count'] = N: IF(['Total_Number_Employee'] <>0,1,0);

Before this

['Total_Number_Employee'] = C: ['Total_Number_Employee_Hold']\['Total_Number_Employee_Count'];

Ideally Total_Number_Employee_Hold element will hold the values of Total_Number_Employee at the days level and Total_Number_Employee_Count will do a conditional check whether 'Total_Number_Employee' holds any value, if it has any value then it will assign value 1 to 'Total_Number_Employee_Count' else 0. So at the Month level sum of all the values of 'Total_Number_Employee' will be divided by the 'Total_Number_Employee_Count' and there by getting the average value.

Hope it helps.