Calculated field: Sum on every row/date if date on row is between start_date and end_date on those rows in dataset

Question & Answer
1

I’m looking to do something similar to below code in SQL:

SELECT dt.date,
dt.record_name,
dt.record_start,
dt.record_end,
dt.record_sales,
(SELECT SUM(sales) FROM dataset WHERE record_start <= dt.date AND record_end >= dt.date) AS all_sales
FROM dataset dt

So basically, sales are constant for each record, for each dat between record_start and record_end. What the calculated field is supposed to output is a sum of the amount for all records that have record_start <= row date and record_end >= row date. Hope screenshot below explains it a bit better. Thanks

Capture
Capture711×268 4.45 KB

2

Hi @variable, You can use level aware calculations (LAC) to achieve what you are look for. Refer to below blog to see some examples of LAC. Hope this helps.

Regards,
Karthik

3

Thank you, @Karthik_Tharmarajan .
However, I’m having trouble figuring this one out. How would LAC work when the condition for aggregation is for date to be between start_date and end_date of those rows in the dataset.
Any help here would be greatly appreciated.
Thanks

4

Any help here would be greatly appreciated.

As I understand it, level aware calculations (LAC) would sum record_sales and group by date. That’s not quite what I need, but rather sum record sales for all records that on that day are active (record_start <= date AND record_end >= date).

This would be pretty easy at the database level, but this is not my dataset, I’m just receiving it and can’t change it at source.