Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]))
Provide sample data...
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))
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
Hi Jonathan,
Thanks for your quick response. But i am taking (Employee ID] only. That was just a typo.
Thanks,
Sumit
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]))
Thanks a lot Manish. It works