Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

23 Replies
YoussefBelloum
Champion
Champion

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

wilsonwebb
Partner - Contributor III
Partner - Contributor III

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

YoussefBelloum
Champion
Champion

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

Anonymous
Not applicable
Author

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 🙂

YoussefBelloum
Champion
Champion

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

Anonymous
Not applicable
Author

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....

rubenmarin

Hi Sanjida, have you tried what Youssef posted above? what's wrong with this?

YoussefBelloum
Champion
Champion

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.

formula.png

the one I gave you is this:

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

Anonymous
Not applicable
Author

Can you pls give me your mail add so that I can share my QVD with you.....