Countifs across BO Webi merge like correlated subquery

Question

I'd like to do a countifs across a merge in BusinessObjects 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.

Here are what the two data sources would look like, as well as the desired output on the report:

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

score 1 · Answer 1 · edited May 13 '23 at 20:00

Amit Kumar from SAP answered my question here. Here is the solution he provided.

« Merge the SID objects and drag in the final table.

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

Create detail variable for Contact date as "V Contact 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 Contact Date];7) And ([V Incident Date]>=[ V Contact Date])) Then 1 Else 0)

Countifs across BO Webi merge like correlated subquery

1 Answers1