Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
ok, I see your problem..
you were making selections on FMonthName and you didn't detach it from the set analysis:
try this exact expression on your AvgSales formula:
sum({<FMonthName,FMonth={">=$(=Max(FMonth)-2)<=$(=Max(FMonth))"}>}SecNetAmount)/3
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 ?
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
In textbox
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)
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....
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
If you don't mind, can you please explain me the formula......
Its consider only one value which i select....but it should also calculate the value of previous two months....
=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