Calculating average across multiple tables

[Former Member]

Hi,

I am relatively new to WebI, so this question may be pretty basic. I tried some searching but could not find a good answer.

I defined four tables in a report, all are based on the same source query and have different filters

First table "Current" - Filter => clearing date is not null

Second table "Cross Table with groups" - Filter => clearing date is null

Third table "Past Due" - Filter => clearing date is null

Fourth table "Tota;" - No filters

I want to insert a percentage field as shown in the picture above that calculates the relative percentage of the value based on the total for January 2012.

How can I do this ?

[Former Member · Accepted solution]

Hi Smith,

Why are you creating multiple tables first? what is the difference in the variables among all these tables?

if it is a single table then you can apply break on the first column i.e. current,past etc

Try this for current

currentmeasure/sum([TotalMeasure] In([MonthYear]))

Here currentmeasure the one which you are displaying in first table and month year is Jan 2012 etc

Total measure is the one which you are displaying in the 3rd table

below is a sample example

	2001	2001	2002	2002	2003	2003
Q1	$2,660,700	0.33	$3,326,172	0.25	$3,742,989	0.25
Q2	$2,278,693	0.28	$2,840,651	0.21	$4,006,718	0.27
Q3	$1,367,841	0.17	$2,879,303	0.22	$3,953,395	0.26
Q4	$1,788,580	0.22	$4,186,120	0.32	$3,356,041	0.22
Sum:	$8,095,814		$13,232,246		$15,059,143

[Former Member · Accepted solution]

Divya,

The first table was filtered to select data where the clearing date is not null.

The second table is filtered to select data where the clearing data is blank, and at the same time I want to group the selected data based on the two categories. (I created a variable based on field Net Due Data using if...else condition)

I will try your formula. Thank you.

[Former Member · Accepted solution]

Hi,

If you want to group the data then you can use the formula which you have created and can apply break on that. This will make the table look like 2 tables even thought it is a single table. and you can play with the data that is there in the entire table