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

[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.

[amitrathi239 · Accepted solution]

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)