Using TOP/LAST

[#1]

Hi. I hope someone can assist with this query... I have a data set that looks like the following:

POST DATE	ORDER#	PRODUCT	AVG COST	QTY PURCH
2012.01.07	1	PROD1	10.5	100
2012.01.08	2	PROD2	7.5	30
2012.01.08	3	PROD3	3	15
2012.01.08	4	PROD2	9	20
2012.01.09	5	PROD4	8.0	70

I have created a slider/calendar object with a variable called vReportDate and need to be able to display the following scenarios:

1. If the report date slider is set to 2012.01.07 (7^th of Jan), the report must show the following:

PRODUCT	AVG COST	TOTAL QTY	STOCK VALUE
PROD1	10.5	100	1050.00

2. If the report date slider is set to 2012.01.08, the report must show the following:

PRODUCT	AVG COST	TOTAL QTY	STOCK VALUE
PROD2	9.0	150	1350.00

The Avg Cost is taken from the most recent transaction of that product, on that date.

I can get all the information except for the Avg Cost. I’ve tried Set-analysis, but cannot use a POST DATE <= ‘vReportDate’… Set Analysis also uses SUM, which I don’t need in this case – I need TOP or LAST..

Any ideas? – thanks!

[#2]

Thanks again for the help and response on this swuehl

I don’t think it’s possible to get some data to you as the data set im working on is over 2GB. I’m going to try and find a discrepancy between the items not showing an AVG COST… because if I remove ALL the columns except for AVG COST, it omits ‘000019’… im not sure why though, so let me check that…

Any ideas, let me know, otherwise I’ll get back to you shortly

[#3]

HI swuehl

Here’s a strange example:

Product#	Avg Cost 2012/01/10	Stock Qty 2012/01/10	Avg Cost 2011/06/14	Stock Qty 2011/06/14
Total		87.00		73.00
000017	22.84000	33.00	-	45.00
000019	-	54.00	65.96	28.00

See 000019 has a value on the 14^th of June, but not current… which is strange, because it should at least pull the avg cost from 14 June…

[#4]

i have an excel extract of the data we are querying... but i cant attach is... how do i attach it (i have copied/pasted it below anyways)

POSTDATE	BRANCH	PROD	AVGCOST	ORDERNO	RECTYPE	ORDTYPE	TOTAL
							R 719,702.27
2012/01/09	2055	000017	22.84	45206	S	SL	R 89.76
2012/01/06	2030	000017	22.915	164900	S	SL	R 92.08
2012/01/05	2040	000017	28.02	118232	S	SL	R 138.12
2012/01/04	2010	000019	87.9225	63120	P	S	R 1,011.00
2012/01/03	1510	000017	22.84	166855	S	SL	R 253.35
2012/01/03	2010	000017	25.71778	228635	S	SL	R 101.34
2012/01/03	2010	000019	65.96	228635	S	SL	R 169.23
2011/12/30	2015	000017	23.155	154618	S	SL	R 50.67
2011/12/29	2055	000019	65.96	44781	S	CR	R 147.76
2011/12/28	2055	000019	65.96	44737	S	SL	R 147.76
2011/12/22	2055	000017	22.84	15912	P	B	R 45.68
2011/12/22	2010	000017	25.19455	228100	S	SL	R 45.68
2011/12/21	2040	000017	25.8	27803	P	S	R 112.08
2011/12/21	2040	000019	99.57667	27803	P	S	R 238.76
2011/12/20	2030	000019	59.97333	32989	P	B	R 131.92
2011/12/19	2010	000019	65.96	227973	S	SL	R 131.92
2011/12/17	2055	000017	22.84	44381	S	SL	R 47.20
2011/12/15	2030	000019	48	163502	S	SL	R 338.46
2011/12/14	2020	000019	65.96	34841	P	B	R 65.96
2011/12/14	2010	000019	65.96	227661	S	SL	R 467.07
2011/12/13	2040	000019	59.97	118062	S	SL	R 153.75
2011/12/13	2010	000019	65.96	227442	S	SL	R 65.96
2011/12/12	2010	000017	24.83231	227274	S	SL	R 460.40
2011/12/12	2010	000019	65.96	227274	S	SL	R 1,230.00
2011/12/09	2045	000017	25.17	30592	P	B	R 44.64
2011/12/09	2010	000017	23.60435	224926	S	SL	R 46.33
2011/12/09	2010	000019	65.96	227049	S	SL	R 307.50
2011/12/08	2010	000017	23.52917	226882	S	SL	R 44.64
2011/12/07	2015	000017	23.236	30109	P	B	R 45.68

[#5]

HI swuehl

I found a pattern in the ‘gaps’ – see below – it seems that if the date is not the exact date set, then it ignores the entry… Could this be something in the script? I am going to try declare the “max(…” as a variable and see if that helps.

Product#	Avg Cost 2012/01/10	Stock Qty 2012/01/10	Last Tran Date	Avg Cost 2011/09/12	Stock Qty 2011/09/12	Last Tran Date
Total		1 494   382.83	2012/01/09		1 551   582.43	2011/09/12
000017	22.84000	33.00	2012/01/09	22.84	46.00	2011/09/12
000019	-	54.00	2012/01/04	63.01267	41.00	2011/09/12
000020	-	94.00	2011/12/09	6.39	59.00	2011/09/12
000021	-	62.00	2011/12/30	6.64	38.00	2011/09/12
000024	30.59000	782.00	2012/01/09	30.59	789.00	2011/09/12
000025	-	6.00	2011/11/25	43.06	4.00	2011/09/12
000026	-	51.00	2011/11/26	-	8.00	2011/08/24
000027	-	27.00	2011/12/14	380.87	18.00	2011/09/12
000030	-	3.00	2011/11/26	280	1.00	2011/09/12
000031	-	7.00	2011/12/06	-	6.00	2011/08/16
000032	308.28000	11.00	2012/01/09	-	10.00	2011/09/08
000034	71.81000	195.00	2012/01/09	76.47	189.00	2011/09/12
000035	112.33500	2.00	2012/01/09	-	5.00	2011/08/12
000036	-	2.00	2011/10/11	-	2.00	2011/08/23
000037	50.55000	9.00	2012/01/09	-	21.00	2011/09/08
000038	-	0.00	2011/11/02	-	2.00	2011/05/04
000039	-	33.00	2012/01/05	-	34.00	2011/09/10
000041	-	0.00	2011/08/29	-	0.00	2011/08/29
000042	-	3.00	2011/11/26	-	3.00	2011/08/03
000044	-	64.00	2012/01/06	751.38824	115.90	2011/09/12
000045	-	72.00	2012/01/06	27.06	37.00	2011/09/12
000046	-	0.00	2011/11/16	-	0.00	2011/06/26
000049	-	0.00	2011/12/08	-	2.00	2011/08/17
000050	63.60000	7.00	2012/01/09	-	8.00	2011/08/31
000051	-	11.00	2011/12/22	-	18.00	2011/07/27
000052	84.26000	56.00	2012/01/09	75.07	40.00	2011/09/12
000054	-	5.00	2012/01/03	526.03	10.00	2011/09/12
000056	340.54727	239.75	2012/01/09	364.2899	225.12	2011/09/12

[swuehl]

I think I know, we only retrieve the Avg Cost for the last POST DATE globally available, but we need to do this per product. I think you could support that task with the data model (i.e. script changes) or maybe with a different chart object expression. Could you please upload your excel file (Upload is available using the advanced editor, which I think is standard, if you edit an existing post, or when adding a post, there is a link in the upper right corner)?

[#7]

Thanks swuelh - here you go - This is only for products 00017/19 - like in the above example

[#8]

FirstSortedValue

({$<[POST DATE]= {"<=$(vReportDate)"} > } AVG_COST, -ORDER#)

[#9]

sorted it out - thanks swuehl

[swuehl]

I came up with almost the same shortly after.

Then I noticed something else:

You stated:

The Avg Cost is taken from the most recent transaction of that product, on that date.

If you use your above expression, you select all POSTDATEs smaller equal your vReportDate in your set expression, then get the AVG_COST ordered by ORDER#. If your highest Order number is not on the latest POSTDATE, your results are not correct.

I noticed that with vReportDate = 2011-04-27 and BR_HI_PART_NO =000019 (BR_HI_BRANCH = 2045).

The lastest Date in above selected Time range is 2011-04-18 with an AVG_COST of zero and Order# 26870.

But there are higher Order# on other dates in that range (for whatever reason), so your expression returns 65.96

So I introduced a combined field

num(POSTDATE)&'.'&num(BR_HI_ORDERNO,'00000000') as DATEONO

and used that for the FirstSortedValue.

Please see also attached.

Regards,

Stefan

[#11]

Wow - you are a genius swuehl - I created a variable with the 'new field' DATAONO and it works 100% now.. Thanks again man!