periodToDateSumOverTime CONTEXTUAL_NESTED_AGGREGATION

Q&A
,
1

Hi there,

I’m trying to build a simple table with the following columns:

MONTH(posting_date), merchant_name, sales, chargebacks, %chargebacks

The last column is calculated as follows:

Given the current month an merchant_name => chargebacks/sales

I understand this should be the calculated field formula:

abs(
periodToDateSumOverTime(
sum({total_chargeback_amount}),
{posting_date},
MONTH
)
)/
periodToDateSumOverTime(
sum({total_purchase_amount}),
{posting_date},
MONTH
)

But I receive CONTEXTUAL_NESTED_AGGREGATION.

What I’m doing wrong??

2

Hello @Jordi_Nebot_Carda , welcome to the Quicksight community!

It’s probably happening because you are using two sum functions on top of each other. You could try running breaking up those calculations into their parts, creating their own calculated fields, and then retrying this. For example

Calc1 = sum({total_chargeback_amount})
Calc2 = sum({total_purchase_amount})
Calc3 = periodToDateSumOverTime(Calc1, {posting_date},MONTH)/periodToDateSumOverTime(Calc2, {posting_date},MONTH)

Let me know if this works!

1 Like
3

Hi @duncan,

First of all, thanks for your help :slight_smile:

I just did what you said, now the formula looks like this:

abs(
periodToDateSumOverTime(
{_sum_total_chargeback_amount},
{posting_date},
MONTH
)
)/
periodToDateSumOverTime(
{_sum_total_purchase_amount},
{posting_date},
MONTH
)

And each of those new fields are calculated fields with the formulas you mentioned, but the error is still the same.

By the way, is quite similar to the use in the docs: periodToDateSumOverTime - Amazon QuickSight

That’s why I’m quite surprised that this is not working

2 Likes
4

I deleted the visual, created it again, and now it works :S:S:S:S:S:S:S:S

1 Like
5

Hello @Jordi_Nebot_Carda !

Interesting, so the calculated field you created now works in the visual just from deleting it and recreating it?

Or did you try the visual with a new calculated field?