I need to add up the last 30 days for each day, a kind of moving sum. I think that perhaps the sumOver function is the most appropriate, but I was unable to adapt the function. I tried using windowSum, but this formula only takes data that is in the view’s time window
Hi @nicolaslucarangek,
You’re on the right track with using the SumOver function but first, you’ll need to make a second calculated field to recognize the 30 days prior date that you’ll then utilize in the SumOver calculated field. Something similar to the below:
30 Days Prior Date - Calculated Field
addDateTime(-30, 'DD', now())
Then you can use that in your new calculated field:
sumOver(ifelse({30 Days Prior Date} <= {date} AND now() >= {date}, {Value}, NULL), [], PRE_AGG)
Please note, in the second calculated field, ‘30 Days Prior Date’ will be whatever you name the first calculated field.
If you have any further questions, feel free to let me know. Otherwise, if this works for what you’re trying to achieve, please mark as the solution.
Thank you!
Hey @Brett, thanks for help me! However, the sum was only for the last 30 days. Maybe I expressed myself poorly. What I need is that for today (06/21/2024) the sum for that date is all the values of the last 30 days from the 21st (-30d), the sum for tomorrow (06/22/ 2024) is from tomorrow until -30d. Did you understand?
Hi @nicolaslucarangek,
Sorry for the confusion but I believe I may be a little turned around. The calculation that I provided, did it not sum up the last 30 days based on current date?
Do you have a separate section for the ‘tomorrow’ data? Maybe if you could provide some screenshots or upload your data in to Arena, I could dive in a bit more as to what you’d like to achieve!
Thank you
Adding to what @Brett suggested, add the date field in the partition. This will give the sumOver for each date value.
e.g. sumOver(ifelse({30 Days Prior Date} <= {date} AND now() >= {date}, {Value}, NULL), [{date}], PRE_AGG)
Hey, @Brett and @prantika_sinha, thanks for your help so far! I drew a sort of example to help you understand what I need to do. In this example, I have a table with some dates, and I need to add up for that day, the value of this day and the previous 29 days
Thank you for the clarification. You can try to use window sum for this calculation.
Sample formula that you can replicate :
windowSum(sum(sales), [orderdate ASC],29,0)
Just one catch, incase there is a missing date, it wont count it in. This is index based, so works considering you have all days listed. I have not tested this against missing date value.
Hey @prantika_sinha, I tried using windowSum, but you can see that the first month (March) is starting from zero, because the view is filtered by the last 4 months, but the month does not start from zero. The other months are right. Do you have any alternative to resolve this?
Hi @nicolaslucarangek,
Since you’re filtering down your dates, that means you’re filtering out the data from the dates prior to your date range. Therefore, it has nothing to compare it to from dates prior to since they’ve now been filtered out. I believe that’s why you’re encountering that issue with the first month.
Hi @nicolaslucarangek, besides using custom SQL – I’m out of ideas. Check out some of the functions. Also, try using the Amazon Q Assistant on the function documentation page.
Another approach is using Generative BI, you can use natural language prompts to create calculated fields within QuickSight, as shown in the following image. For more information about calculated fields in analyses, see Adding calculated fields.
In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)
Hi @nicolaslucarangek, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!