on 11-09-2017 6:55 PM
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:
The other data source, known as "Incidents", has the following columns:
I would like to create a report in Webi based on the "Contacts" data source (maybe using merge, detail variables) with the following columns:
[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.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you, Amit! Wonderful! Works like a charm.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
In the document summary check the option extended merged dimension and see if it will work or not.
Or share some sample data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.