Average Last 3 months

Question & Answer
1

Hi team,

I would like to create a calculated field to calculate the avg related of the last 3 months:

image
image652×662 10.6 KB

Could you help me please?
Rgds
Eduardo

2

You can do something like this:

avgOver(distinct_count({iduser}),[truncDate('MM',{datetime})])

Then you can do a relative filter for the last 3 months.

image
image478×962 40.5 KB

1 Like
3

Hi Max,

Thank you very much for your help…

Your suggestion help me in parts…
I understood that the I need to apply a filter to get the avg for the 3 last months. Indeed I need to create a AVG last 3 months to compare with current value.
For instance, today is Aug-23, so I need to get the avg of May/Jun/Jul to compare with current value to see if it is above or below …ok

Let me know if you have any questions,

Rgds
Eduardo

4

Could 30 days work?

Then you are looking to group by 30 days.

I think something like this would work.

1 Like
5

Hi @EduardoMarchesin -
Did Max’s suggestions help solve your issue? If so, could you mark this as a Solution. If not, please let us know what additional assistance you may need.

6

Hi Kellie,

The Max’s suggestion help me in parts…
I understood that the I need to apply a filter to get the avg for the 3 last months. Indeed I need to create a AVG last 3 months to compare with current value.
For instance, today is Aug-17, so I need to get the avg of May/Jun/Jul to compare with current value to see if it is above or below …ok

Let me know if you have any questions,

Thank you very much,
Eduardo

7

@EduardoMarchesin -
I approached the problem by first calculating if the date was in the last 3 months. I then used that calculation in a 2nd calculation to get the avg. You can see my calculations and results in the screenshot. There may be other ways to approach this, but this solution is one option.

last3months
last3months1564×558 170 KB

8

Hi Kellie,

Thank you very much for your help!!

I will test and let you know!!

Thanks
Eduardo

9

Hi Kellie,

Your solution has been solved partially, I would like to have a vision showing the current value (Aug-2023) compared with the AVG Last3Months. Like that:

image

This vision has a filter with only the current period (Aug-2023), using your solution it gets a value of this period and divides by 3… e.g. 192159 / 3 = 64053 and so on…

The values for the 3 last months are:
image

The AVG should be (324798+305045+235646) / 3 = AVG = 288496

Please, let me know if you have any questions,

Rgds
Eduardo

10

Hi,
If you do not need to include current month as one of the 3 months, you can modify the calculated field that Kelly showed above to :
ifelse(dateDiff({Order Date}, now(), ‘MM’) <=3 AND dateDiff({Order Date}, now(), ‘MM’) >0, Sales, 0)