Inventory first sorted value

[#1]

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

[whiteline]

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

[#3]

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