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.

[Gysbert_Wassenaar]

FirstSortedValue(aggr(Sum(distributed),_StoreKey1,scene_item_date),-aggr(scene_item_date,_StoreKey1,scene_item_date))

[aldo-tgh]

Hi Gysbert,

Thanks for your answer.

Almost there.

Please note the Total for the expression should be 20 and not 16.

What is missing?

Thanks,

Aldo.

[Gysbert_Wassenaar]

Set the total mode for the expression to Sum of Rows instead of Expression Total

[aldo-tgh]

That's great, but still need to work around the expression in order to use it on a List Box.

Aldo.

[Gysbert_Wassenaar]

Why do you want it in a list box? You won't be able to select it.

[aldo-tgh]

my user need to see the total number of distributions into selected  stores.

Based on last session for each store.

[Gysbert_Wassenaar]

That's the total in the table... precisely where the context is too so the user can understand where it comes from and what it means.

[aldo-tgh]

I mean TEXT Box - not List Box - Sorry!

[Gysbert_Wassenaar · Accepted solution]

Ok, that makes a bit more sense.

='Total number of distributions based on the last session per store: ' & sum(aggr(FirstSortedValue(aggr(Sum(distributed),_StoreKey1,scene_item_date) ,-aggr(scene_item_date,_StoreKey1,scene_item_date)),_StoreKey1))