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

[ttal7878]

Attched

[sunny_talwar · Accepted solution]

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

[ttal7878]

it's working in the example qvw,

but in my real qvw it's not working

i don't know want i am doing wrong

can you please look at my code and maybe you can see' what i'm doing wrong

I can't send the real qvw.

=Sum(TOTAL <[Department Description],SalesDepartmentName,IsOnline>

{<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}

Aggr(

(Sum({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}Sum))

/

(Sum({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}aggr(Max({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}First_Try_Amount),unique_num,Department,UpType,IsOnline)))

,[Department Description],SalesDepartmentName,IsOnline,Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year))

/180

[sunny_talwar]

You are using = sign in your aggr dimension... I think you need to remove them

=Sum(TOTAL <[Department Description],SalesDepartmentName,IsOnline>

{<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}

Aggr(

(Sum({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}Sum))

/

(Sum({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}aggr(Max({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}First_Try_Amount),unique_num,Department,UpType,IsOnline)))

,[Department Description],SalesDepartmentName,IsOnline,Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year))

/180

Try this instead

=Sum(TOTAL <[Department Description],SalesDepartmentName,IsOnline>

{<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}

Aggr(

(Sum({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}Sum))

/

(Sum({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}aggr(Max({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}First_Try_Amount),unique_num,Department,UpType,IsOnline)))

,[Department Description],SalesDepartmentName,IsOnline,Calendar_TRX.Day,Calendar_TRX.Month,Calendar_TRX.Year))

/180

[ttal7878]

It's work!!! thank you so much!

if it's not too much trouble,can you explain why you added the line (before the aggr)

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

[sunny_talwar]

I am not sure if it is needed or not, but a lot of the times, you would need the inner set analysis in the outer set analysis to make sure that your inner set analysis is not over-written by the outer one...

=Sum(TOTAL <[Department Description],SalesDepartmentName,IsOnline>

{<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}

Aggr(

(Sum({<Calendar_TRX.Day=,Calendar_TRX.Month=,Calendar_TRX.Year=,cctranzresponse={'000'},Calendar_TRX.Calendar_Date={">=$(Vlast180days)<=$(VmaxTRXdate)"}>}Sum))

....

Inner is in Green and Outer in Red

[ttal7878]

thank you stalwar1‌!!!