summarize details and calculate averages on groups

building reports webintelligence
1

WEBI XI 3.1
We have raw data from a table which looks as follows:

Group   Account  Amount  Date
Group1  Account1 50      1.1.2010
Group1  Account2 20      1.1.2010
Group1  Account3 30      1.1.2010
Group2  Account4 500     1.1.2010
Group2  Account5 200     1.1.2010
Group2  Account6 300     1.1.2010

Group1  Account1 150      2.1.2010
Group1  Account2 120      2.1.2010
Group1  Account3 130      2.1.2010
Group2  Account4 3500     2.1.2010
Group2  Account5 3200     2.1.2010
Group2  Account6 3300     2.1.2010

We need to create report which shows
a) total Amount by groups and dates
b) Average Amount per group

Group  Date       TotalAmount 
Group1 01.01.2010 100
       02.01.2010 400
Group1 Average    250

Group2 01.01.2010 1000
       02.01.2010 10000
Group2 Average    5500

Grand Average     5750

in example above Group1 Average is calculated as sum of Amounts of all Group1 dates (100+400) divided by count of dates (01.01 and 02.01 - 2 dates) = (100+400) / 2 = 250

count of dates may vary due to user input - user selects dates in “between” prompt

How to do the same calculation in WEBI report?

alex1st (BOB member since 2009-10-07)

2

Drag and drop Column Group, Date and Amount on the report.
Put Break on Group Column.
Sum the Amount : =Sum([Amount]) (required if agregation for the measure is none at Universe level)
Select Amount column now and aggrgate it for Average (you will see formula in bar as =Average(Sum([Amount])) )

Hope this help!

SP1584 (BOB member since 2011-01-07)

3

Thanks a lot!

Sum the Amount : =Sum([Amount]) (required if agregation for the measure is none at Universe level)
Select Amount column now and aggrgate it for Average (you will see formula in bar as =Average(Sum([Amount])) )

agregation for the measure is SUM,
so =Average([Amount]) gives the average needed:


Group  Date       TotalAmount 
Group1 01.01.2010 150 
       02.01.2010 150
       03.01.2010 150
Group1 Average    150

but if we remove “Date” column from the report:

  • all dates become summarized: 450
  • and averages become equal to these sums:

Group  Date       TotalAmount 
Group1            450
Group1 Average    450 --wrong

how to get this result:


Group  Date       TotalAmount 
Group1            450 - sum of amount between dates
Group1 Average    150 - average between dates (and not between many rows of raw data)

alex1st (BOB member since 2009-10-07)

4

Hi again,

You are correct on this. Seems calculation context require in this but I dont have any idea at this moment if removing the date column will show correct results :(. Lets wait for experts comments.

SP1584 (BOB member since 2011-01-07)

5

Hi,

In universe level define sum(Amount) for amount object.

Drag GroupName, Date and Amount you will get like this

Groupname Date Amount
Group1 1/1/10 100
Group1 2/1/10 400
Group2 1/1/10 1,000
Group2 2/1/10 10,000

Select the group and click break and then select amount column and click average.

You will get your result which is

Groupname Date Amount
Group1 1/1/10 100
2/1/10 400
Group1 Average: 250

Groupname Date Amount
Group2 1/1/10 1,000
2/1/10 10,000
Group2 Average: 5,500

Average:	2,875

Regards,
Suthija
[/code]

suthijap (BOB member since 2011-03-01)