Skip to Content
0

How do I make a countifs across Business Objects webi merge, like correlated subquery?

Nov 09, 2017 at 06:55 PM

246

avatar image
Former Member

I'd like to do a countifs across a merge in Business Objects webi. I have two different data sets, which are merged on dimension "SID". One data source which would be the source for the outer query, known as "Contacts", has the following columns:

  • SID (number, dimension)
  • Contact Date (date, dimension)
  • Contact Type (string, dimension)

The other data source, known as "Incidents", has the following columns:

  • SID (number, dimension)
  • Incident Date (date, dimension)

I would like to create a report in Webi based on the "Contacts" data source (maybe using merge, detail variables) with the following columns:

  • SID (number, dimension)
  • Contact Date (date, dimension)
  • Contact Type (string, dimension)
  • Incidents Influenced (VARIABLE countifs of records in "Incidents" where the following is true:)

[Incidents].[SID] = [Contacts].[SID]

[Incidents].[Incident Date]<=([Contact].[Contact Date]+7)

[Incidents].[Incident Date]>=[Contact].[Contact Date]

Any thoughts? I've been trying to do a detail variable with count() where, but this doesn't seem to be working. I am using BI Platform 4.2, Webi. I have researched many websites external to Stack overflow, such as Variable to count Merged dimensions and count formula in webi report.

I have also looked at these two stack overflow articles, Business Objects CountIf by cell reference and Business Objects WEBI 3 universes in one report. However, none of these show how, at least in a way I am able to understand, how to overcome a context error that seems to occur whenever I create detail variables and measures across the SID merge.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
AMIT KUMAR
Nov 10, 2017 at 05:13 PM
1

try with below steps.

Merge the SID objects and drag in the final table.

Drag Contract Date & Contract Type objects in the final table.

Create detail variable for Contract date as "V Contract Date" and for Incident date as "V Incident Date".

Right click on the table->Format table-> checked the checkbox "Show rows with empty dimensions"

Drag below formula for calculated column and see.

=Sum(If([V Incident Date]<=RelativeDate([ V Contract Date];7) And ([V Incident Date]>=[ V Contract Date])) Then 1 Else 0)


untitled.png (42.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 09, 2017 at 10:05 PM
0

Here is some sample data to demonstrate what I am trying to do. Only, I would have two data sources in Webi as "Contacts" and "Incidents" that would provide the data for the desired output.

The Incidents would be counted to see which match the SID, are greater than or equal to the Contact Date, and are less than or equal to the Contact Date + 7 days.

CONTACTS
SID     Contact Date     Contact Type
1        8/1/2017        ONB
1        8/15/2017       SCB
2        8/14/2017       ONB
3        8/19/2017       ONB
4        9/1/2017        SCB

INCIDENTS
SID     Incident Date
1       8/1/2017
1       8/7/2017
1       8/9/2017
1       8/21/2017
1       9/1/2017
1       10/1/2017
2       8/15/2017
4       9/2/2017

**DESIRED OUTPUT ON REPORT
SID     Contact Date     Contact Type     Incidents Influenced
1        8/1/2017        ONB               2
1        8/15/2017       SCB               1
2        8/14/2017       ONB               1
3        8/19/2017       ONB               0
4        9/1/2017        SCB               1

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 10, 2017 at 07:20 AM
0

impression-of-sap-merge-on-countifs-problem.png

See the attached image which demonstrates what I'm trying to do, just instead of Excel, I'd like to use two different Webi data sources to do a countifs across a merge on SID.

Share
10 |10000 characters needed characters left characters exceeded
AMIT KUMAR
Nov 09, 2017 at 07:37 PM
0

In the document summary check the option extended merged dimension and see if it will work or not.

Or share some sample data.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 13, 2017 at 04:50 PM
0

Thank you, Amit! Wonderful! Works like a charm.

Share
10 |10000 characters needed characters left characters exceeded