Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

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

Highlighted
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

Highlighted
Creator
Creator

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 🙂

Highlighted
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

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted
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

Highlighted
Creator
Creator

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

Highlighted
Champion
Champion