topic Re: Inventory first sorted value in QlikView

Inventory first sorted value

Fri, 05 Oct 2012 14:52:31 GMT

Hello All,

I've been working with the below expression to capture inventory for an item at a point in time (end date LY is a variable).

one thing to note about the source table is that it inserts a new row when there is a OH change.

sum(aggr(FirstSortedValue({<inv_cal_dt={"<=$(EndDateLY)"} >} oh_qty, (-inv_cal_dt + (Rand()*.001))), sku, store_num))

the expression has been working beautifully until we introduced inventory price status. Below is an example of inventory data for an item and store.

inv price status	inv_cal_dt	oh_qty
C	9/29/2011	0
C	2/20/2011	1
R	2/13/2011	1
R	2/10/2011	1

when using end date LY of 9/10/2011, the straight table returns 1 unit, but displays the inv cal date of 9/29/11. i woulld have expected to see 2/20/2011. Any help on where is the disconnect?

Also (and more importantly), I would like to enhance the expression to include inv price status. For example, as of the variable date, display the OH only if the status in that row is R.

I’ve tried these, but they don’t do exactly what I need. They both display the 1 oh_qty with different inv cal dates and price status.

I would have expected to see nothing because even though there is 1 unit as of 9/10/2011 the price status is not R.

sum(if(Inv_prc_status_cd='R',aggr(FirstSortedValue({<inv_cal_dt={"<=$(EndDateLY)"} >} oh_qty, (-inv_cal_dt + (Rand()*.001))), sku, store_num)))

sum(aggr(FirstSortedValue({<inv_cal_dt={"<=$(EndDateLY)"}, Inv_prc_status_cd={'R'} >} oh_qty, (-inv_cal_dt + (Rand()*.001))), sku, store_num))

Please help,

Sara

Re: Inventory first sorted value

whiteline — Sat, 06 Oct 2012 10:07:06 GMT

Hi.

You use only sku, store_num as dimensions to calculate firstsortedvalue() so the inv price status is discarded by the first expression, just add it:

=sum(aggr(FirstSortedValue({<inv_cal_dt={"<=$(EndDateLY)"} >} oh_qty, (-inv_cal_dt + (Rand()*.001))), sku, store_num, Inv_prc_status_cd))

To involve only 'R' statuses, try this:

=sum({<Inv_prc_status_cd={"R"}>} aggr(FirstSortedValue({<inv_cal_dt={"<=$(EndDateLY)"} >} oh_qty, (-inv_cal_dt + (Rand()*.001))), sku, store_num, Inv_prc_status_cd))

Re: Inventory first sorted value

Mon, 08 Oct 2012 21:15:58 GMT

that results in 2 rows on the report, see below. it should only display C because its the most current.

the second expression also did not work for me.

Inv_prc_status_cd	inv_cal_dt	sum(aggr(FirstSortedValue({<inv_cal_dt={"<=9/10/2011"} >} oh_qty, (-inv_cal_dt + (Rand()*.001))), sku, store_num, Inv_prc_status_cd))	sum( {<Inv_prc_status_cd={'R'} >} aggr(FirstSortedValue({<inv_cal_dt={"<=9/10/2011"} >} oh_qty, (-inv_cal_dt + (Rand()*.001))), sku, store_num, Inv_prc_status_cd))
C	2/20/2011	1	0
R	10/7/2010	1	1
		2	1