13 Replies Latest reply: Apr 25, 2016 10:19 AM by gustavo gajardo

# 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 !

• ###### Re: sum two maximum from the start

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

• ###### 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!

• ###### Re: sum two maximum from the start

Sunny any idea?

I need to finish this urgent work

• ###### Re: sum two maximum from the start

I am checking it my friend

• ###### 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

• ###### 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 !

• ###### 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;

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
• ###### 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

• ###### Re: sum two maximum from the start

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

Thanks yo very much

• ###### Re: sum two maximum from the start

It seems to be working, isn't it?

• ###### Re: sum two maximum from the start

Yes Sunny !

It seems to be working perfectly, the part of "Rangesum( FirstSortedValue(Sale, -Sale- recID/100000,1), FirstSortedValue(Sale, -Sale- recID/100000,2))" its amazing.

However, I dont understand -Sale- recID/100000.

I've been all afternoon trying to apply the function to another problem and I could not. Can you explain me ?

Sorry for your time Sunny, I am new in this.

Thank you very very much.

• ###### 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

• ###### Re: sum two maximum from the start

Thank you very much Stefan

Thank you for your explanations and time!