topic Re: Help with average in QlikView

Help with average

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

Hey guys,

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

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

Re: Help with average

manas_bn — Fri, 14 Jul 2017 00:00:50 GMT

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:

Hope this helps!

Manas

Re: Help with average

sunny_talwar — Fri, 14 Jul 2017 00:19:31 GMT

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)

Re: Help with average

jcampbell474 — Fri, 14 Jul 2017 00:24:44 GMT

This expression should work:

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

Re: Help with average

satishsure — Fri, 14 Jul 2017 00:38:09 GMT

"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