Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone !
I have a list with sales date in this format (eliminating the time, so there are recurring dates) and the sales.
Date | Sale | |
---|---|---|
| 10 | |
| 11 | |
| 12 | |
| 12 | |
| 5 | |
| 9 | |
| 15 | |
| 8 | |
| 8 | |
| 3 | |
| 20 | |
| 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:
Date | Sale | |
---|---|---|
| 21 | |
| 24 | |
| 24 | |
| 27 | |
| 27 | |
| 27 | |
| 35 |
I have tried many "measures" to make this graphic but no results. Any suggestions?
Regards !
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
Not entirely sure how you got this output? Would you be able to elaborate the logic?
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:
DATE | Two highest sales | |
| 10 + 11 | |
| 12 + 12 | |
| 12 + 12 | |
| 15 + 12 | |
| 15 + 12 | |
| 15 + 12 | |
| 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!
Sunny any idea?
I need to finish this urgent work
I am checking it my friend
May be swuehl can guide us. I am having difficult calculating the second max. I will share what I have
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 !
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-2015 | 21 | 21 |
03-08-2015 | 23 | 24 |
04-08-2015 | 23 | 24 |
05-08-2015 | 27 | 27 |
06-08-2015 | 27 | 27 |
07-08-2015 | 27 | 27 |
10-08-2015 | 35 | 35 |
Aren't you one of the best this community have got. This is amazing. Thanks for helping us out
It does not work in my file Qlik sense. What I can do ?
Thanks yo very much