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

sum two maximum from the start

Hello everyone !

I have a list with sales date in this format (eliminating the time, so there are recurring dates) and the sales.

DateSale
31-07-2015
10
31-07-2015
11
03-08-2015
12
03-08-2015
12
04-08-2015
5
04-08-2015
9
05-08-2015
15
06-08-2015
8
07-08-2015
8
10-08-2015
3
10-08-2015
20
10-08-2015
9

I want to make a line graph showing me the sum of the two highest sales since the beginning of time starting from the first date. Thus becomes:

DateSale
31-07-2015
21
03-08-2015
24
04-08-2015
24
05-08-2015
27
06-08-2015
27
07-08-2015
27
10-08-2015
35

I have tried many "measures" to make this graphic but no results. Any suggestions?

Regards !

1 Solution

Accepted Solutions
swuehl
MVP
MVP

The second argument -Sale-recID/100000 is the sort weight for the Sales field. It's used to distinguish between ties, i.e. in your sample between the repeated values of 12.

We somehow need QV to find '12' as max number, but also '12' as second largest number in your series of values, e.g. on 03-08-2015.

So I've added a small unique number, record number divided by 100000. (the added number should be much smaller than the smallest difference between your Sale values).

Hope this makes sense,

Stefan

View solution in original post

13 Replies
sunny_talwar

Not entirely sure how you got this output? Would you be able to elaborate the logic?

Not applicable
Author

Sure, sorry if you misunderstood.

For each day, we proceed to add the two largest sales that have occurred since the beginning of the following form:

DATETwo highest sales

31-07-2015

10 + 11
03-08-2015
12 + 12
04-08-2015
12 + 12
05-08-2015
15 + 12
06-08-2015
15 + 12
07-08-2015
15 + 12
10-08-2015
20+15

Thus, for example for the day 7-8-2015, the two largest sales have been to date are 15 and 12 so that the result would be 15 + 12 = 27 for that day.

I hope you will understand better.

Thank you!

Not applicable
Author

Sunny any idea?

I need to finish this urgent work

sunny_talwar

I am checking it my friend

sunny_talwar

May be swuehl‌ can guide us. I am having difficult calculating the second max. I will share what I have

Not applicable
Author

Sunny thank you very much!

But I am using Qlik sense. However, thank you very much for the time spent in my problem and if you get something not hesitate to send it to me.

Thanks again !

swuehl
MVP
MVP

Maybe like this?

Table:

LOAD *, recNo() as recID Inline [

Date, Sale

31-07-2015, 10

31-07-2015, 11

03-08-2015, 12

03-08-2015, 12

04-08-2015, 5

04-08-2015, 9

05-08-2015, 15

06-08-2015, 8

07-08-2015, 8

10-08-2015, 3

10-08-2015, 20

10-08-2015, 9

];

TMP:

LOAD DISTINCT Date as AsOfDate

Resident Table;

JOIN LOAD Date

Resident Table;

AsOf:

NoConcatenate

LOAD * Resident TMP

WHERE Date <= AsOfDate;

DROP TABLE TMP;

AsOfDate Rangesum(Max(Sale),Max(Sale,2)) Rangesum( FirstSortedValue(Sale, -Sale- recID/100000,1), FirstSortedValue(Sale, -Sale- recID/100000,2))
35 35
31-07-20152121
03-08-20152324
04-08-20152324
05-08-20152727
06-08-20152727
07-08-20152727
10-08-20153535
sunny_talwar

Aren't you one of the best this community have got. This is amazing. Thanks for helping us out

Not applicable
Author

It does not work in my file Qlik sense. What I can do ?

Thanks yo very much