FirstSortedValue with TOTAL by dimension

[aldo-tgh]

Hi Guys,

Need help with the following.

my data set has the following fields:  Storekey, Sessionkey, Itemkey, scene_date, and distributed, like:

_StoreKey1	_SessionKey1	scene_date	Sum(distributed)
			92
254	50a	07/01/2014	50
254	87c	08/01/2014	4
704	798	07/01/2014	22
704	02a	14/01/2014	16

I am trying to sum distributed in last scene_date  of each storekey.

In the example attached,

last session for store 254 is on 08/01/2014 the count=4

last session for store 704 is on 14/01/2014 the count=16

I need to be able to create both, a table and a list box, like:

_StoreKey1	# of distributed items	Last Session	# of distributed items on LAST SESSION
	92		20
254	54	08/01/2014	4
704	38	14/01/2014	16

and also need to show that calculation (green field) into a List Box.

I am trying to use FirstSortedValue, something like:

FirstSortedValue( TOTAL <_StoreKey1,Sessionkey> distributed ,-scene_date)

but getting NULL since there are more than 1 resulting lines for each combination of Store & Session & Scene_Date because of Itemkey.

How do I work it around?

Thanks in advance for any help.

Aldo.

[aldo-tgh]

Thanks!