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
It is already working the way you're expecting
=Sum({<YearMonth={">$(=Date(AddMonths(Max(Date#(YearMonth,'YYYYMM')),-3),'YYYYMM'))<=$(=Max(YearMonth))"}>} SecNetAmount)/3
the greyed part is the part that will look back at the last three previous months.
the only thing that can cause this to not work as expected is the format of your field YearMonth
Hi Sanjida,
My suggestion would be you create three variables for each month. You then create a simple sum of the value with a set analysis for each of the month in them. This is then divided by 3
didn't read this
in the expression above we use set analysis with a range of values to define what we take on the YearMonth Field
we evaluate correctly the format of the field using Date#
we use max (optional, in case you select nothing, it will select the max value)
we use addmonths to roll back in previous months
and we use Date to format the ouput because Addmonths give us a DATE field, we should re format it as YYYYMM..
take a look at the help to understand each function very well
Can you help me on this formula to calculate the sales value of current month:
=sum({$<YearMonth =Date(AddMonths(Max(Date#(YearMonth,'YYYYMM')),-0),'YYYYMM')>}SecNetAmount)
i got error on that formula.... if i can fix it then i will add three sum using the formula and also roll back to find the value of previous two months...
Thanks in advance 🙂
I'm here to help but you didn't give us your feedback on your last request (last 3 months) and on your understanding of the formula
I did understand the formula... the formula works fine when i individually find the month....using the below expression which you provided:
=Date(AddMonths(Max(Date#(YearMonth,'YYYYMM')),-1),'YYYYMM')
but when i trying to find the average sales it shows the average sales of that month which i selected, didn't consider the other 3.......
as an example :
May- 100, Apr-50, Mar-25
If i select May average sales should be 100+50+25/3=58.33 but it shows 100+100+100/3=100..as my selected value is may....
Hi Sanjida, have you tried what Youssef posted above? what's wrong with this?
Logic, because the formula you put above wasn't the one I've gave you previously on the post.
with your formula (which is incomplete, just a part of the range in the set analysis), you're looking back 1 month.
the one I gave you is this:
Sum({<YearMonth={">$(=Date(AddMonths(Max(Date#(YearMonth,'YYYYMM')),-3),'YYYYMM'))<=$(=Max(YearMonth))"}>} Sales)/3
Can you pls give me your mail add so that I can share my QVD with you.....