
Re: Calculating average sales
Sunny Talwar May 23, 2016 12:33 PM (in response to Franco Giovannini)Try this expression:
=Sum(SalesAmount)
/
(((Year(Max(SalesDate)) * 12) + Month(RangeMin(Max({1}SalesDate), YearEnd(Max(SalesDate))))) + 1 
If(Year > Year(StartSalesDate),
(((Year(YearStart(Min(SalesDate)))*12)+Month(YearStart(Min(SalesDate))))),
(((Year(StartSalesDate)*12)+Month(StartSalesDate)))))
When nothing is selected:
When 2015 is selected
When 2016 is selected:
I think two places it doesn't match is when nothing is selected you have 50/12 as 4.61, where as I think it should be 4.16667 (or 4.17)
and second place is when 2016 is selected B's max date seems to be 01/02/2016, so it should be 2 months, not 3, right?
Attaching the application for you to play around with.
Best,
Sunny

Community_217947.qvw 155.2 K

Re: Calculating average sales
Stefan Wühl May 23, 2016 1:09 PM (in response to Sunny Talwar )Attached a different solution.
I assumed that you want to count the months up to today's months for 2016 or if nothing is selected.
(Currently you are only counting months up to the max month in your sales fact table, but you don't have records for all months).
You can adjust this by changing the vEndDate variable in the script.
I've also added a kind of master calendar which makes it easier to get the min and max month numbers per selection and also allows to draw your data on a time base without missing months.

comm217947.qvf 192.0 K


Re: Calculating average sales
Sunny Talwar May 23, 2016 1:18 PM (in response to Sunny Talwar )Did not realize this was Qlik Sense
Attaching a QlikSense sample application as well

Community_217947.qvf 176.0 K



Re: Calculating average sales
Franco Giovannini May 25, 2016 8:27 AM (in response to Franco Giovannini)Thank you Swuehl and Sunny T, both your solutions works perfectly, I was fighting with an expression similar to the Sunny T one, when I realized in fact that i was missing a "total", and that for this reason the result was dependent from the selection. Thank you again for your answers, I've learnt a lot from both!