Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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

1 Solution

Accepted Solutions
Highlighted
Champion
Champion

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

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

View solution in original post

23 Replies
Highlighted
Champion
Champion

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

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 ?

Highlighted

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

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

Highlighted
Creator
Creator

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

In textbox

Highlighted
Champion
Champion

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

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)

Highlighted
Creator
Creator

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

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

Highlighted
Champion
Champion

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

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

Highlighted
Creator
Creator

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

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

Highlighted
Creator
Creator

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

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

Highlighted
Creator
Creator

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

=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