Sum Currency out of a large table with certain criteria

[Rainer]

Hello everybody,

I have an order table fetched from Salesforce Analytics with a lower million record count.

In this table I have several records for the same order. One record for each change, that has happened to this order with a corresponding keydate.

In a pivot I now need to sum only one of these records (the max date of an order which is smaller than a specified Date)

I hope I could explain clearly my need? Has anyone an idea how to approach this?

Thankful for any idea.
Rainer

 

[Aladin Akyurek]

Care to post a sample of 5 rows representative of your data along with the desired outcome?

 

[Rainer]

Of course. Here is a simplified dummy recordset.

Line	Ordernumber	Date of Order	KeyDate	Amount
1	4711	01.01.18	01.01.18	1,000.00 €
2	4711	01.01.18	15.01.18	2,000.00 €
3	4711	01.01.18	10.02.18	1,500.00 €
4	4813	15.01.18	15.01.18	3,500.00 €
5	4813	15.01.18	17.01.18	3,450.00 €
6	4813	15.01.18	10.02.18	3,600.00 €
7	4813	15.01.18	01.03.18	3,650.00 €
If the Reporting Keydate is 01.03.18 it should sum up lines 3 and 7.
If the Reporting Keydate is 15.01.18 it should sum up lines 2 and 4.
If the Reporting Keydate is 10.01.18 it should sum up line 1, as the second order wasn't created at that time..

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Thx
Rainer