topic Re: Urgent help needed on Aggregation Function in QlikView

Urgent help needed on Aggregation Function

Mon, 03 Nov 2014 16:19:47 GMT

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

Re: Urgent help needed on Aggregation Function

MK_QSL — Mon, 03 Nov 2014 16:31:57 GMT

Provide sample data...

Re: Urgent help needed on Aggregation Function

JonnyPoole — Mon, 03 Nov 2014 16:38:53 GMT

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))

Re: Re: Urgent help needed on Aggregation Function

Mon, 03 Nov 2014 18:02:31 GMT

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

Re: Re: Urgent help needed on Aggregation Function

Mon, 03 Nov 2014 18:03:58 GMT

Hi Jonathan,

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

Thanks,

Sumit

Re: Urgent help needed on Aggregation Function

MK_QSL — Tue, 04 Nov 2014 08:29:39 GMT

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]))

Re: Urgent help needed on Aggregation Function

Tue, 04 Nov 2014 10:37:23 GMT

Thanks a lot Manish. It works