Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Please visit COVID-19 group to keep up the discussion. GO TO GROUP
Highlighted
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
Highlighted
MVP
MVP

Re: sum two maximum from the start

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
Highlighted

Re: sum two maximum from the start

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

Highlighted
Not applicable

Re: sum two maximum from the start

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!

Highlighted
Not applicable

Re: sum two maximum from the start

Sunny any idea?

I need to finish this urgent work

Highlighted

Re: sum two maximum from the start

I am checking it my friend

Highlighted

Re: sum two maximum from the start

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

Highlighted
Not applicable

Re: sum two maximum from the start

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 !

Highlighted
MVP
MVP

Re: sum two maximum from the start

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
Highlighted

Re: sum two maximum from the start

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

Highlighted
Not applicable

Re: sum two maximum from the start

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

Thanks yo very much