Calculate the average of the averages

[ttal7878]

Hi,

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

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

the 2^nd shows the number of clearing attempts,

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

I need to create a new column (4^th) that shows the average: add up the sums displayed in the 3^rd 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

[sunny_talwar]

Try this

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

[ttal7878]

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)

[sunny_talwar]

What are you selecting and what is your expected table

[ttal7878]

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

Department	SaleDepartment	First Try $	App $	App Ratio by First Time $	New Column
A	YY	200	200	1	1/180
A	XX	1500	2300	1.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)"}>}

[Kushal_Chawda]

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

[ttal7878]

I manage to build the calculation:

Thanks to your and stalwar1‌‌ help!

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

[sunny_talwar]

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

[ttal7878]

It's not working

[sunny_talwar]

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