Show Last Period Value

[Anonymous]

Hi,

I have a problem about showing last period value in pivot table.

Here is my data:

Area	SalesID	Teritory	District	Week	Value
A	1122	01	001	1601	5
A	1122	01	001	1603	10
A	1122	01	001	1605	22
A	1122	01	002	1604	5
A	1122	01	002	1606	7
A	1122	01	002	1602	10

I have two different district. And maximum week Of District 001 is 1605, on the other side maximum week of district 002 is 1606.

So i want to create a pivot table like this :

Area	SalesID	Teritory	District	=sum(Value)	max(Week)	Last Week Value
A	1122	01	001	37	1605	0
A	1122	01	002	22	1606	7

District 002 shown right value (7), but district 001 return zero value (0).

I'm using this expression on my "Last Week Value" column : =sum({<Week={$(=max(Week))}>}Value)

The result i expect is

Area	SalesID	Teritory	District	=sum(Value)	max(Week)	Last Week Value
A	1122	01	001	37	1605	22
A	1122	01	002	22	1606	7

Can anyone give me the solution ? I also attached my sample QVW file.

Thanks in advance.

[Anil_Babu_Samineni]

But, For 002 you are expecting 7 or 5 ??

[Anonymous]

Hi,

For 002 i expect 7, because i want to show value on week 1606 (last week of district 002)

Thanks

[Anil_Babu_Samineni]

Oh, Sorry i have not read properly. May be this?

FirstSortedValue(Value, Aggr(Week, Value))

[Anonymous]

Hi,

I have try your solution,

It works on my sample data.

But it is not working on my real data, unfortunately i can't share my data, because it's confidential.

Do you have another solution ?

Thanks in advance

[Anil_Babu_Samineni]

Atleast data need to reply. Would it possible sample data. Or

You may explain bit more