Hey! I am trying to calculate a 6-month average for a calculated field that calculates the percentage of total. This table also filters out two columns, but the percent of total updates to always equal 100%. I am looking to find the 6-month average of this percentage contribution of total but I am getting nesting errors for the various formulas I have tried. Does anyone know how to solve this? I would then want to use this previous 6-month average to see how it deviates from the current month.
Hello @sofiserr !
If possible, could you share the calculation that you’ve made that keeps running into errors? That might help narrow down where the issue is coming from.
At a glance from your post, I would recommend checking out the following if you haven’t already:
1 Like
Hi Duncan. Thanks for getting back to me! My issue with periodToDateAvgOverTime is that I can’t figure out how to select 6 months as my time frame. With windowAvg, it wants to calculate with the amount of rows above and below, but I want there to be the averages over the months and I have thousands of varying rows of data so I cannot count it. I was wondering if maybe you knew how to do a sort of dense rank function that would assign my ship months a number, and perhaps I could then use the windowavg function to the dense rank field.
Hey @sofiserr !
Could you try using a parameter with periodToDateAvg to set the end date as today as well as the period?
Something like this: periodToDateAvg(Sales,{Order Date},MONTH,${Previous6Months})
Where Parameter set up looks like this:
Let me know if this works!
Hi Duncan. I am now getting this error:
Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed.
The field that I am trying to average is a percent contribution to a total amount. Do you know how to fix this issue?
Hi @sofiserr,
To help look into this in more detail, would you be able to share an example of the data and the expected output that you’re trying to achieve?
Also, are you able to share the calculated field that calculates the percentage of total?
Many Thanks,
Andrew
Here is a sample of the calculations that I am trying to do. I would like a contribution to total amount that is averaged.
On quicksight, I have a field that uses:
percentOfTotal
(
sum({bill}),
[{date}]
)
This is how I get the amounts that are seen in in the last pivot table. I then use quicksight’s filtering function to get rid of the data that I do not want to contribute (in this example it is returns)
Hello @sofiserr - Since a few months have passed from your original post I am going to archive this question. If you still need assistance with this please post a new question in the QuickSight community and link to this topic. Due to the community focus on responding to questions with 0 replies, this will ensure you are a top priority for help from one of our QuickSight experts. Thank you!