Skip to Content
0

Issue with counting Rows

Jul 14, 2017 at 08:55 AM

77

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

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
10 |10000 characters needed characters left characters exceeded
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
10 |10000 characters needed characters left characters exceeded
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
10 |10000 characters needed characters left characters exceeded
Mats Nilsson 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
10 |10000 characters needed characters left characters exceeded
Mats Nilsson 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
10 |10000 characters needed characters left characters exceeded

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)
0