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
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.
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
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.
I am going to archive this topic since it has been open for an extended period of time. If you would still like assistance with this, or if you have a new QuickSight question, feel free to post a new topic in the community so you will be at the top of the priority list for a response from one of our QuickSight experts. Thank you!
