how to calculate of last three months average sales based on my selection?

[Anonymous]

Dear All,

How can I calculate of last three months average sales based on my selection?

As an example,if I select Aug-18 then calculated average sale should be of '(June-18 + July -18 + Aug - 18)/ 3'

Thanks in advance

[YoussefBelloum]

Hi,

the solution depends on the output you're looking for

do you want to display the last three months on the table with the average on every cell ? do you want to display the average on a textbox ?

would you be able to describe the expected output ?

[rubenmarin]

Hi, it can vary based on date formats, if you have a master calendar... but it can be something like:

Sum({<Date={">$(=AddMonths(Max(IdFecha),-3))<=$(=Max(IdFecha))"}>} Sales)/3

[Anonymous]

In textbox

[YoussefBelloum]

Ruben already gave you the solution, just tell us what is the exact format of the field on which you're making your selections (month year maybe)

[Anonymous]

Actually i dont have any Date field... only month and year. As an example : 201801,201802,201803 and so on.... so i did not find solution on Ruben's expression....

[YoussefBelloum]

Ruben talks about Date-related fields, like the one you're using

let's say your year month field is called: YearMonth.. try this (based on the format you shared above):

Sum({<YearMonth={">$(=Date(AddMonths(Max(Date#(YearMonth,'YYYYMM')),-3),'YYYYMM'))<=$(=Max(YearMonth))"}>} Sales)/3

[Anonymous]

If you don't mind, can you please explain me the formula......

[Anonymous]

Its consider only one value which i select....but it should also calculate the value of previous two months....

[Anonymous]

=Sum({<YearMonth={">$(=Date(AddMonths(Max(Date#(YearMonth,'YYYYMM')),-3),'YYYYMM'))<=$(=Max(YearMonth))"}>} SecNetAmount)/3

but also need to consider the sales value of previous two months