Aggregating dimension based on Max month

[#1]

I have a table in the format

Emp ID	Role	Month	Amount
102001	Line Manager	1	100
102001	Line Manager	2	140
102001	Line Manager	3	220
102001	Line Manager	4	120
102001	Program Manager	5	230
102001	Line Manager	6	120
102001	Line Manager	7	100

When I create a straight table in qlikview. i get the following output

Emp ID	Role	Amount
102001	Line Manager	800
102001	Program Manager	230

But my requirement is to get total amount for an employee irrespective of his Role and ideally Role should be what is there in current month.

1. If i have not selected any month my role will be Line Manager (for July) and total amount will be 800+230=1030

Output:

Emp ID	Role	Amount
102001	Line Manager	1030

2. If i have selected Jan-May, role will be Program Manager and Amount will be sum(amount) form Jan-May.

Output:

Emp ID	Role	Amount
102001	Program Manager	810

So i should get only one record for the employee even if i have Role in dimension.

Any suggestions how can i do this ?

Thanks,

Sumit

[rubenmarin]

Hi Sumit, check attachment.

Expression for Role: only({<Month={$(=Max(Month))}>} Role)

Expression for Amount: Sum({<Month={"<=$(=Max(Month))"}>} Amount)

[PradeepReddy]

Try something like this...

Role : only({<Month={$(=Max(Month))}>} Role)

Amount : sum({<Month={'>= $(=min(Month)) <=$(=max(Month))'}>}Amount)

[Peter_Cammaert]

Since you will be using an expression for the Role column, the other expression for Amount can revert back to:

=sum(Amount)

since Sumit specified that he will sum only those months that are explicitly selected...