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.

[TABLE="width: 606"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]Line[/TD]
[TD="align: right"]Ordernumber[/TD]
[TD="align: right"]Date of Order [/TD]
[TD="align: right"]KeyDate[/TD]
[TD="align: right"] Amount [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4711[/TD]
[TD="align: right"]01.01.18[/TD]
[TD="align: right"]01.01.18[/TD]
[TD] 1,000.00 €[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4711[/TD]
[TD="align: right"]01.01.18[/TD]
[TD="align: right"]15.01.18[/TD]
[TD] 2,000.00 €[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4711[/TD]
[TD="align: right"]01.01.18[/TD]
[TD="align: right"]10.02.18[/TD]
[TD] 1,500.00 €[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4813[/TD]
[TD="align: right"]15.01.18[/TD]
[TD="align: right"]15.01.18[/TD]
[TD] 3,500.00 €[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4813[/TD]
[TD="align: right"]15.01.18[/TD]
[TD="align: right"]17.01.18[/TD]
[TD] 3,450.00 €[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]4813[/TD]
[TD="align: right"]15.01.18[/TD]
[TD="align: right"]10.02.18[/TD]
[TD] 3,600.00 €[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]4813[/TD]
[TD="align: right"]15.01.18[/TD]
[TD="align: right"]01.03.18[/TD]
[TD] 3,650.00 €[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]If the Reporting Keydate is 01.03.18 it should sum up lines 3 and 7.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]If the Reporting Keydate is 15.01.18 it should sum up lines 2 and 4.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]If the Reporting Keydate is 10.01.18 it should sum up line 1, as the second order wasn't created at that time..[/TD]
[/TR]
</tbody>[/TABLE]

Thx
Rainer