cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor

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)

Answers (4)

Answers (4)

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

amitrathi239
Active Contributor
0 Kudos

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

Or share some sample data.