Average Calculation by day

Q&A
, , ,
1

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 |

image
image1352×502 24 KB

2

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?

3

Hi, I haven’t tried it yet.
What would this syntax look like?

4

image
image1409×582 39.1 KB

image
image925×350 8.42 KB

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.

5

Hi @jcolucci,
Interesting; could you share the calculated field that you created for this?

6

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.

Moving Average
Moving Average1160×918 54.5 KB

7

Good morning, Brett!
Any feedback, did you manage to find a solution to the problem?
Thx Jéssica

8

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

9

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!

10

Hi Brent, good afternoon!
I used the windowAvg function and the calculation worked.
Thank you!