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

1 Solution

Accepted Solutions
Highlighted
Champion
Champion

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

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

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

In textbox

Highlighted
Champion
Champion

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

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

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

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

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

=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