Skip to Content
avatar image
Former Member

Issue with counting Rows

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Jul 14, 2017 at 09:15 AM

    Hi Mats - would an =COUNT formula work?

    See below screen shot:

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 14, 2017 at 11:37 AM

    Hi,

    use exception aggregation on count value :

    Regards,

    Anand

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 17, 2017 at 05:54 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 14, 2017 at 11:13 AM

    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....:-(

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 14, 2017 at 01:47 PM

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

    Add comment
    10|10000 characters needed characters exceeded