Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Average of sales por month

Hello friends,

I need your help. I need show average of sales por month. But the average have that considerate the month and 11 month ago. Dimension is month of year 2015

 

YearMonthSales(Uni)
20141120
20142130
20143150
2014480
2014590
20146100
20147110
20148140
20149130
201410160
201411170
201412180
20151130
20152150
20153140
20154110
2015580
20156110
20157140
20158120
20159140
201510180
201511200
201512210

Expected Result:

JanFebMarAprilMayJuneJulyAguSepOctNovDec
131133132134133134137135136138140143

thanks for your help

1 Solution

Accepted Solutions
Not applicable

Re: Average of sales por month

Thanks so much friend and got what he wanted. This is the final expression.

Only({<Year = {$(=Max(Year))}>}Aggr(RangeAvg(Above(TOTAL Sum({1} Sales), 0, 12)), Year, MonthName))

12 Replies
MVP
MVP

Re: Average of sales por month

Can you elaborate on the logic to calculate the average once again?

Not applicable

Re: Average of sales por month

Of course,


Por example:

January 2015 = SUM(Dec 2014, Nov 2014, Oct 2014, Sep 2014, Agu, July, Jun,May,Apr,Mar,Feb 2014) / 12

February 2015 = SUM(Jan 2015, Dec 2014, Nov 2014, Oct 2014, Sep, Agu, July, Jun,May,Apr,Mar 2014)   / 12

March 2015 = SUM(Feb 2015, Jan 2015, Dec 2014, Nov 2014, Oct, Sep, Agu, July, Jun,May,Apr 2014)   / 12


thanks for answering

MVP
MVP

Re: Average of sales por month

This?

Capture.PNG

Not applicable

Re: Average of sales por month

Yes, this is ok. But I can not open qvw file in my qlik sense desktop. Which is the set analysis that you used ?

oknotsen
Honored Contributor III

Re: Average of sales por month

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!
MVP
MVP

Re: Average of sales por month

Expression used:

=Only({<Year = {$(=Max(Year))}>}Aggr(RangeAvg(Above(TOTAL Sum(Sales), 0, 12)), Year, MonthName))

Not applicable

Re: Average of sales por month

Thanks for your help. But I have a problem. The year I have as a filter and when I select a specific year to evaluate the expression does not work. I can change to make it work ?

MVP
MVP

Re: Average of sales por month

How about this:

=Only({<Year = {$(=Max(Year))}>}Aggr(RangeAvg(Above(TOTAL Sum({<Year = {"$(='>=' & Max(Year) - 1)"}>} Sales), 0, 12)), Year, MonthName))

Not applicable

Re: Average of sales por month

The expression not found. If I do not select the year, the grafic is well.

Captura.PNG

But I select the year the chart is wrong.

Captura2.PNG

Then I add data from 2016, and I want to select the year al get the average sales for the selected year.