Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using TOP/LAST

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 (7th 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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try something like

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

View solution in original post

19 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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

Try something like

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

Not applicable
Author

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

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

swuehl
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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

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.