Announcements
cancel
Showing results for
Did you mean:
Creator

## Calculate the average of the averages

Hi,

I have a simple bar chart that displays the sum of sales:

There are 3 columns: the 1st column shows the sum of sales that has been approved,

the 2nd shows the number of clearing attempts,

and the 3rd shows an average of the first 2 columns. (column1st  /column 2nd)

I need to create a new column (4th) that shows the average: add up the sums displayed in the 3rd column for the last 180 days, and divide them by 180. (which means an average of the averages).

In the qvw I attached, for department 'A', the calculation should be:0.5+0.8333 divide180=0.0074

Thanks

1 Solution

Accepted Solutions
MVP

May be try this

=Sum(TOTAL <[Department],SaleDepartment> {<Day=,Month=,Year=,cctranzresponse={'000'},Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>} Aggr(

(Sum({<Day=,Month=,Year=,cctranzresponse={'000'},Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}Sale_Sum))

/

(Sum({<Day=,Month=,Year=,Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}aggr(Max({<Day=,Month=,Year=,Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}First_try),ID,Department)))

,[Department],SaleDepartment,Day,Month,Year))

/180

16 Replies
MVP

Try this

=Sum(TOTAL <Department> Aggr(Sum(Sale_APP)/Sum(Sale_Attemp), Department, Day))/180

Creator
Author

Thank stalwar1

but I find it difficult to add to the formula filter of the time dimension,

please see the calculation of my real qvw

A calculation that works but does not filter the dimension of time:

=Sum(TOTAL <[Department Description],SalesDepartmentName,IsOnline> Aggr(Sum (if(cctranzresponse=000,Sum,0))

/

Sum(aggr(Max(First_Try_Amount),unique_num,Department,UpType,IsOnline)), [Department Description],IsOnline, SalesDepartmentName,Calendar_TRX.Day,Calendar_TRX.Month,Calendar_TRX.Year))/180

The calculation that does not work because I added the dimension of time and also because I was trying to build it with  set of analysis:

=Aggr(Sum(TOTAL <[Department],SalesDepartment,IsOnline>

{<CalendarDay=,CalendarMonth=,CalendarYear=,cctranzresponse={000},CalendarDate={">=\$(Vlast180days) <=\$(VmaxTRXdate)"}>}Sum)

/(aggr(Sum(max({<CalendarDay=,CalendarMonth=,CalendarYear=,CalendarCalendar_Date={">=\$(Vlast180days) <=\$(VmaxTRXdate)"}>}First_Try_Amount),unique_num,Department,UpType,IsOnline))

,

[Department],IsOnline, SalesDepartment,CalendarDay,CalendarMonth,CalendarYear)

/180)

MVP

What are you selecting and what is your expected table

Creator
Author

I create a new QVW which I tried to fit it as closely as possible to my real qvw

my requested outcome:

create a new column that adds up the sums displayed in the 'App Ratio by First Time \$' column for the last 180 days, and divide them by 180.

for example:

the user selects "Date"

The data that will display for Department='A' :

Date = 30.08.2017

VmaxTRXdate = 30.08.2017

Vlast180days =03/03/17

DepartmentSaleDepartmentFirst Try \$App \$App Ratio by First Time \$New Column
AYY20020011/180
AXX150023001.533(3.33+1+1.2)/180

I created the calculation for the new column, but I want to add the filter of the time dimension
And make the calculation to set analysis

Date={">=\$(Vlast180days) <=\$(VmaxTRXdate)"}>}

try below

Variable change

VmaxTRXdate=date(max(Date),'YYYY-MM-DD')

Vlast180days=date(VmaxTRXdate-180,'YYYY-MM-DD')

First try \$

=Sum({<Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}aggr(Max({<Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}First_try),Department,SaleDepartment))

App \$

=Sum ( {<cctranzresponse={'000'},Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}Sale_Sum)

App Ratio by First Time \$

=Column(1)/Column(2)

New column

=Column(3)/180

Creator
Author

I manage to build the calculation:

But I'm having trouble with the "Date dimension", when there is no date filtering, the calculation works fine, but if the filter works, I get an outcome of 0

I use the following calculation:

=Sum(TOTAL <[Department],SaleDepartment> Aggr(

(Sum({<Day=,Month=,Year=,cctranzresponse={'000'},Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}Sale_Sum))

/

(Sum({<Day=,Month=,Year=,Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}aggr(Max({<Day=,Month=,Year=,Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}First_try),ID,Department)))

,[Department],SaleDepartment,Day=,Month=,Year=))

/180

MVP

Try this

=Sum(TOTAL <[Department],SaleDepartment> {<Day=,Month=,Year=,cctranzresponse={'000'},Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>} Aggr(

(Sum({<Day=,Month=,Year=,cctranzresponse={'000'},Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}Sale_Sum))

/

(Sum({<Day=,Month=,Year=,Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}aggr(Max({<Day=,Month=,Year=,Date={">=\$(Vlast180days)<=\$(VmaxTRXdate)"}>}First_try),ID,Department)))

,[Department],SaleDepartment,Day=,Month=,Year=))

/180

Creator
Author

It's not working

MVP

Can we see this problem in your attached file? I have not checked, but I just want to make sure that it is there in one of the files you have attached

Community Browser