SumOver within WindowSum

[deliverit]

Hi,
I am trying to run a LTM (Last 12 Months) analytics on my dataset using date range filters. To do this I use
windowSum(sum(Revenue),[SaleDate ASC],12,0)

However the windowSum calculates the window sum within the filter range. I require the windowSum to calculate the window sum outside the date range filter.

windowSum(sumOver(sum(Revenue), [SaleDate ASC], PRE_AGG),[SaleDate ASC],12,0)

I get this error

For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.<

I then remove the aggregate and then get this error

The metric of the table calculation should be aggregated. <

My question is can you run a sumOver within a WindowSum?

Thanks in advanced.

[Max]

@deliverit

windowSum(sumOver(sum(Revenue), [SaleDate ASC], PRE_AGG),[SaleDate ASC],12,0)

Have you tried changing the aggregation?

windowSum(sumOver(sum(Revenue), [SaleDate ASC], POST_AGG_FILTER),[SaleDate ASC],12,0)

Or if you need to use the PRE_AGG then you will need to wrap the sumOver in an aggregation.

windowSum(sum(sumOver(Revenue, [SaleDate ASC],PRE_AGG)),[SaleDate ASC],12,0)

[deliverit]

Hi Max,
Thanks for your attempt to help. Unfortunately both suggestions didn’t work.
Your first suggestion the calculated fields still run the window within the filtered range and the second solution blows the sum out quite significantly.
This is what I am currently using

windowSum(sumOver(sum(GTV),[{order_time} ASC]),[{order_time} ASC],12,0)

However once I add the level-aware calculations I get the error.

[Max]

What do you mean by calculating it “outside” the filtered range?

Do you not want the last twelve months? You want everything else?

[deliverit]

So I am using a date range filter to present the data

Based on the calculated field I am using, it starts the calculation within the filtered range and the GTV value for the last 12months starts at 0. The data set goes all the way back to 2010 so I need to be able to calculate the last 12months outside of the filtered range. PRE_FILTER would be able to resolve what I want to achieve however I am getting errors using windowSum.
Hope this makes sense.

Cheers

[MKuhns01]

You can’t achieve your expected results by filtering on {order_time}

What you need to do is create a new calculated field like so:

minOver(min({order_time}), [{order_time}])

And then use the calculate field within your visual.

This pushes Quicksight to first calculate the window sum, and then apply the filtering for the visual after the sum is calculated.

You can read more about it here:

How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?

We conceptually need to ‘hide’ those values and not filter them. To do this we will manipulate where this filter happens in the query pipeline, and by using a Table Calculation, we can make it happen all the way at the end, after the YoY calcs have already been computed. Write a calc like this: minOver(min({order date}), [{order date}]) And then put your filter on that. Should work! Before: [image] After: [image]