0

# Issue with counting Rows

Jul 14, 2017 at 08:55 AM

81

Former Member

Hi, Working in SAP BusinessObjects Analysis for Office v 2.4.0.65048 and have spent a lot of hours trying to figure out the following:

I need to calculate the number of "Invoices with Order" and the number of Invoices without orders ("Wild Invoices") per vendor and department, as you can see in the attached image. The SUM of the value is calculated but I also need to sum up the count. Any help appreciated.

capture.jpg (128.8 kB)

Tammy Powlas
Jul 14, 2017 at 09:15 AM
0

Hi Mats - would an =COUNT formula work?

See below screen shot:

1mats.jpg (16.7 kB)
Share
Anand Kumar Jul 14, 2017 at 11:37 AM
0

Hi,

use exception aggregation on count value :

Regards,

Anand

test.jpg (62.4 kB)
test2.jpg (37.1 kB)
Share
Anand Kumar Jul 17, 2017 at 05:54 AM
0

hi,

Where are you using BOOLEAN expression? Are you using BOOLEAN expression in the AFO workbook or BOOLEAN expression in bex query designer.

As Tammy suggested you can use the functionality in Properties of the formula define in BEx Query .

Regards,

Anand.

Share
Former Member Jul 14, 2017 at 11:13 AM
0

Thx Tammy, Well the problem is that I need to get the COUNT result integrated in the sheet in the same way as value is calculated, ie summed up per (i) Spend Stream, (ii) Supplier, (iii) Department and that it adopts accordingly every time I update the time range. So I started by adding (the yellow) columns with a BOOLEAN expression in order to populate each row with a 1 when there a value in the cell. But the problem is that the Result rows are also following the BOOLEAN calculation and do not understand it should count the sum instead.

So the overview should look like this:

And when I expand the rows to show the details it shall look like the figure below, ie where the 7 invoices in total for Vendor C are summed up separately per Vendor and per Department and site:

But instead in reality it look like below……with no sum of the count values....:-(

capture2.jpg (38.6 kB)
capture3.jpg (89.0 kB)
capture4.jpg (85.7 kB)
Share
Former Member Jul 14, 2017 at 01:47 PM
0

Thx Anand, I cannot find the "Formel ändern" functionality. Could you pls attach the image showing where this is located?

Show 1 Share

Mats - it looks like he is showing you the Properties of the key figure in the BEx Query Display panel - and recommending that you adjust the settings there:

2mats.jpg (52.1 kB)