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!

[swuehl]

Are you using a table chart with dimension PRODUCT? Then you could try for AVG COST expression:

=FirstSortedValue({<[POST DATE]= {'$(vReportDate)'} > } [AVG COST], -ORDER#)

Hope that helps,

Stefan

[#3]

Will try now and let you know - thanks for the fast response!

[#4]

Hi Swuehl - thanks for the response again. I tried:

FirstSortedValue({<[POST DATE]= {'$(vReportDate)'} > } [AVG COST], -ORDER#)

And it worked perfectly fine… however, I have the issue is that if there are a few gap dates. I have moved the date 2012.01.09 in the above example to 2012.01.11 (see below)

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.11	5	PROD4	8.0	70

So when they select report date 2012.01.10, it needs to show avg cost from 2012.01.08

My immediate reaction would be [POST DATE] < = {'$(vReportDate)’} but that doesn’t work … L

What would you recommend?

[swuehl · Accepted solution]

Try something like

FirstSortedValue({<[POST DATE]= {'$(=Date(max({<[POST DATE] ={"<=$(vReportDate)"}>} [POST DATE])))'} > } [AVG COST], -ORDER#)

[#6]

wow - unfortunately this doesnt work Swuehl... what exactly does the code do?

There might be a semi-colon or comma out...

[swuehl]

Well, it should work. Please see attached.

I am just looking for the max POST DATE smaller or equal vReportDate, then retrieve the AVG value for that date.

Probably the format returned from Date() function is not compatible with your format used for POST DATE? Is your standard date format different from 'YYYY.MM.DD' ?

[#8]

Awesome - thanks swuehl. I removed the 'Date(...' and all works fine now - thanks for the help!!

[#9]

Hi swuehl

The solution isn’t 100%.. I’m getting a few ‘gaps’ in my list of products. Then only when I select the actual product, it gives me values. See below – I’ve added another AVG COST for the variable vDateToday which equals “=date(today())”

Product#	Avg   Cost 2012/01/09	Stock   Qty 2012/01/09	Stock   Value 2012/01/09	Avg   Cost 2011/06/16	Stock   Qty 2011/06/16	Stock   Value 2011/06/16
Total		1 494 382.83	R 306 722 076.04		1 251 399.46	R 40 157 408.73
000017	22.84000	33.00	R 753.72	-	45.00	-
000019	-	54.00	-	-	28.00	-
000020	-	94.00	-	-	38.00	-
000021	-	62.00	-	-	45.00	-
000024	30.59000	782.00	R 23 921.38	-	620.00	-
000025	-	6.00	-	-	7.00	-
000026	-	51.00	-	-	8.00	-
000027	-	27.00	-	-	11.00	-
000030	-	3.00	-	-	0.00	-
000031	-	7.00	-	-	7.00	-
000032	308.28000	11.00	R 3 391.08	-	50.00	-
000034	71.81000	195.00	R 14 002.95	-	98.00	-
000035	112.33500	2.00	R 224.67	-	4.00	-

Then I select product ‘000019’ and it gives me a result:

Product#	Avg Cost 2012/01/09	Stock Qty 2012/01/09	Stock Value 2012/01/09	Avg Cost 2011/06/16	Stock Qty 2011/06/16	Stock Value 2011/06/16
Total		54.00	R 4   747.82		28.00	R 1   846.88
000019	87.92250	54.00	R 4 747.82	65.96	28.00	R 1 846.88

Is there something I’m missing in my set analysis?

Thanks again for your help!

[swuehl]

Is it possible that you post a small sample here? I assume that we have ambiguous values for some lines, unless one product# is selected. Thus is would be good having a closer look to your data model.