topic Re: FirstSortedValue with TOTAL by dimension in QlikView

FirstSortedValue with TOTAL by dimension

aldo-tgh — Wed, 22 Oct 2014 12:30:35 GMT

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.

Re: FirstSortedValue with TOTAL by dimension

Gysbert_Wassenaar — Wed, 22 Oct 2014 12:46:57 GMT

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

Re: FirstSortedValue with TOTAL by dimension

aldo-tgh — Wed, 22 Oct 2014 12:52:22 GMT

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.

Re: FirstSortedValue with TOTAL by dimension

Gysbert_Wassenaar — Wed, 22 Oct 2014 12:55:47 GMT

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

Re: FirstSortedValue with TOTAL by dimension

aldo-tgh — Wed, 22 Oct 2014 12:57:42 GMT

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

Aldo.

Re: FirstSortedValue with TOTAL by dimension

Gysbert_Wassenaar — Wed, 22 Oct 2014 13:07:24 GMT

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

Re: FirstSortedValue with TOTAL by dimension

aldo-tgh — Wed, 22 Oct 2014 13:12:20 GMT

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

Based on last session for each store.

Re: FirstSortedValue with TOTAL by dimension

Gysbert_Wassenaar — Wed, 22 Oct 2014 13:17:19 GMT

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.

Re: FirstSortedValue with TOTAL by dimension

aldo-tgh — Wed, 22 Oct 2014 13:17:45 GMT

I mean TEXT Box - not List Box - Sorry!

Re: FirstSortedValue with TOTAL by dimension

Gysbert_Wassenaar — Wed, 22 Oct 2014 13:19:57 GMT

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

Re: FirstSortedValue with TOTAL by dimension

aldo-tgh — Wed, 22 Oct 2014 13:27:34 GMT

Thanks!