Urgent help needed on Aggregation Function

[#1]

I have an expression

(sum({$<Phase_Productivity_Category={'Development'}>}[Sum of TotalHours])

  *

(10/(max(Month)-min(Month)+1)))

When i am adding this to a straight table with Employee ID as dimension it is giving me correct value. But i have to use it in a text object to populate values based on employee ID. i.e. i have to aggregate it based on employee id.

If an employee has data between Feb-Oct his Max Month will be 10 and Min Month will be 2, if it is between Jan-Sep Max month will be 9 and Min month will be 1 and the values should be calculated accordingly but the current expression is giving max month as 10 and min month as 1 for all the employees.

I am using the below expression to aggregate

Sum(Aggr( (sum({$<Phase_Productivity_Category={'Development'}>}[Sum of TotalHours])

  *

(10/(max(Month)-min(Month)+1))) , Emplyee ID))

but this is not giving me correct value.

Can someone tell me the right way to do it.

Thanks,

Sumit

[MK_QSL]

Provide sample data...

[JonnyPoole]

i do not know with 100% certainty what the right expression with your app will be, but i can tell you with 100% certainty that you cannot reference a field that has a name with a space with it without enclosing in square brackets

in the following expression, change 'Emplyee ID'  to  [Emplyee ID] please...

Sum(Aggr( (sum({$<Phase_Productivity_Category={'Development'}>}[Sum of TotalHours])

  *

(10/(max(Month)-min(Month)+1))) , Emplyee ID))

[#4]

Hi Manish,

The sample data is in this format

Employee ID	Month	Sum of Hours	Phase
1	Jan	10	Development
1	Feb	21	Development
1	Oct	23	Development
2	Feb	12	Development
2	Mar	15	Development
2	Sep	17	Development
3	Apr	18	Development
3	May	20	Development
3	Jul	25	Development
3	Aug	23	Development
4	Mar	12	Development
4	May	28	Development
4	Jun	21	Development

My desired output should be

(10+21+23)*(10/(10-1+1)) + (12+15+17)*(10/(9-2+1))+ (18+20+25+23)*(10/(8-4+1))+ (12+28+21)*(10/(6-3+1)) = 433.5

but my current expression is returning (10+21+23+12+15+17+18+20+25+23+12+28+21)*(10/(10-1+1)) = 245.

Basically instead of taking Max Month and Min Month employee wise and multiplying with the corresponding amount its taking the overall sum and multiplying with the overall Max Month and Min Month which is wrong.

Thanks,

Sumit

[#5]

Hi Jonathan,

Thanks for your quick response. But i am taking (Employee ID] only. That was just a typo.

Thanks,

Sumit

[MK_QSL · Accepted solution]

Create one Inline Table as below

Load * Inline

[

  Month, MonthID

  Jan, 1

  Feb, 2

  Mar, 3

  Apr, 4

  May, 5

  Jun, 6

  Jul, 7

  Aug, 8

  Sep, 9

  Oct, 10

  Nov, 11

  Dec, 12

];

Now use below expression

=SUM(Aggr(SUM([Sum of Hours])/(10/(Max(MonthID)-Min(MonthID)+1)),[Employee ID]))

[#7]

Thanks a lot Manish. It works