Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
joshrussin
Creator III
Creator III

Help with average

Hey guys,    

I am trying to get the average of Total_F for the year, NOT including this month.

average.PNG

The correct number I need to achieve is:  306,922

It is also including the 0 and making my answer 245,538

Need some help getting current year, excluding current month. Here is what I am trying.

=(Avg({$<Month = {"$(!=Month(Today()))"}, Year = {"$(=Year(Today()))"}>} Total_F))

1 Solution

Accepted Solutions
sunny_talwar

Try this may be

Avg({$<[Non Service_F] = {"$(='>=' & Date(YearStart(Max([Non Service_F])), 'M/D/YYYY') & '<' & Date(MonthStart(Max([Non Service_F])), 'M/D/YYYY'))"}, Month, Year>} Total_F)

View solution in original post

4 Replies
manas_bn
Creator
Creator

Hi Joshua,

Can you create a "MonthYear" Column in the back end with MMM-YYYY format? Then you can use the below expression.

=Avg({$<MonthYear -={"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>} Sales)

Below is the script I used:

//Temp Table

test:

load * inline

[Date, Sales

1/1/2017,10

2/2/2017,10

3/3/2017,10

4/4/2017,10

5/5/2017,10

6/6/2017,10

7/7/2017,2];

// Resident load with MonthYear Column

test1:

load

Date,

month(Date)&'-'&year(Date) as MonthYear,

Sales

resident test;

// drop temp table

drop table test;


Result:

Capture.PNG

Hope this helps!

Manas

sunny_talwar

Try this may be

Avg({$<[Non Service_F] = {"$(='>=' & Date(YearStart(Max([Non Service_F])), 'M/D/YYYY') & '<' & Date(MonthStart(Max([Non Service_F])), 'M/D/YYYY'))"}, Month, Year>} Total_F)

jcampbell474
Creator III
Creator III

This expression should work:

avg({<[Non Service_F]={"<$(=DATE(MonthStart(Today(1))))"}>}Total_F)

satishsure
Contributor
Contributor

"Monthval"  is calculated column contains month names in "MMM" format.Basically comparing the same values in set analysis

=Avg( {$ < Monthval -={"$(=Date(Max(Non_Service_F),'MMM'))"}  >} Total_F).

find the below link for more details.

Excluding values in Set Analysis