topic Using TOP/LAST in QlikView

Using TOP/LAST

Mon, 09 Jan 2012 12:54:32 GMT

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!

Using TOP/LAST

swuehl — Mon, 09 Jan 2012 13:29:02 GMT

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

Using TOP/LAST

Mon, 09 Jan 2012 13:32:38 GMT

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

Using TOP/LAST

Mon, 09 Jan 2012 13:59:20 GMT

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?

Using TOP/LAST

swuehl — Mon, 09 Jan 2012 14:14:30 GMT

Try something like

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

Using TOP/LAST

Mon, 09 Jan 2012 14:21:12 GMT

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

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

Re: Using TOP/LAST

swuehl — Mon, 09 Jan 2012 14:27:39 GMT

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' ?

Using TOP/LAST

Mon, 09 Jan 2012 14:41:00 GMT

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

Using TOP/LAST

Tue, 10 Jan 2012 08:02:37 GMT

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!

Using TOP/LAST

swuehl — Tue, 10 Jan 2012 08:45:13 GMT

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.

Using TOP/LAST

Tue, 10 Jan 2012 08:55:45 GMT

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

Using TOP/LAST

Tue, 10 Jan 2012 09:08:28 GMT

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…

Using TOP/LAST

Tue, 10 Jan 2012 09:20:39 GMT

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

Using TOP/LAST

Tue, 10 Jan 2012 10:46:32 GMT

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

Using TOP/LAST

swuehl — Tue, 10 Jan 2012 11:51:11 GMT

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

Re: Using TOP/LAST

Tue, 10 Jan 2012 12:06:05 GMT

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

Re: Using TOP/LAST

Tue, 10 Jan 2012 13:31:05 GMT

FirstSortedValue

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

Re: Using TOP/LAST

Tue, 10 Jan 2012 13:31:39 GMT

sorted it out - thanks swuehl

Re: Using TOP/LAST

swuehl — Tue, 10 Jan 2012 14:18:49 GMT

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

Using TOP/LAST

Wed, 11 Jan 2012 11:19:28 GMT

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