Good afternoon!
How can I create a calculated field with a simple formula of average of a column of Amount USD by currency and by day.
Example: (1+10+6+234)=251= Average(251)=62,75
|Creation_date|Operation_Currency| Operation_amount_USD |Avg amount USD|
|Oct 17, 2024| |CLP| |174.047,41| | 61.854,28 |
|Oct 17, 2024| |PEN| |11.383,93 | | 61.854,28 |
|Oct 17, 2024| |USD| |131,50 | | 61.854,28 |
Hi @jcolucci,
So to make sure I understand correctly, you’d like to find the average based on the operation_currency and the creation_date?
Have you tried using the avgOver calculation for this instance?
Hi, I haven’t tried it yet.
What would this syntax look like?
I’ve done the syntax and the calculation for 17/10/2024 is correct.
However, when I change the type of visual element from pivotable to stacked combination chart where the bars show the Amount USD and the line shows the average amount USD, it changes the calculation of the average.
Hi @jcolucci,
Interesting; could you share the calculated field that you created for this?
Hi Brett, good morning!
Of course!
avgOver(sum({Operation_amount_USD}),[{Creation_date}]).
This formula is not correct because it does not calculate the moving average.
I need to calculate the 30-day moving average of the amount USD by currency.
Just like the excel table.
Good morning, Brett!
Any feedback, did you manage to find a solution to the problem?
Thx Jéssica
Hi @jcolucci,
I believe the best way to find the 30 day moving average would be to utilize the WindowAvg function.
I built a similar sample in Arena view for you to look at:
Average Calculation by day
Hi @jcolucci,
It’s been awhile since we last heard from you so following up to see if you had any additional questions?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Thank you!
Hi Brent, good afternoon!
I used the windowAvg function and the calculation worked.
Thank you!
1 Like