cancel
Showing results for 
Search instead for 
Did you mean: 

How can I resolve calc context from diferents queries?

Former Member
0 Kudos

Hi Experts,

I have a dubt on a web Intelligence report about sum calc with dimensions from different queries. Let me give you an example.

Imagine we have from one query A:

And from query B:

We have City as merge dimension

And we need this result on the report:

The formula that I would writte to resolve it would be:

=Sum([Revenue] In ([City])) In ([Country])

But as we don't have the dimension Country on the query A I get 900 for all countries.

Can you help me to resolve that?,

Thank you

Accepted Solutions (1)

Accepted Solutions (1)

former_member201488
Contributor
0 Kudos

The solution is quite simple - use:


=forcemerge([Revenue])

Description of Forcemerge:

Includes synchronized (e.g. Merged) dimensions in measure calculations when the dimensions are not in the measure's calculation context.

HTH

NMG

Former Member
0 Kudos

Thank you Neil, I didn't know about that function and it resolves what I need.

Answers (4)

Answers (4)

former_member210032
Active Participant
0 Kudos

Hi Oscar,

Just change the formula

1.variable

Sum1=Sum([Revenue]) Where([City] =[City])

2.variable

Final_Sum=If [City] =[City]) Then ([Sum1]) Else [Revenue]

Regards

Mustafa

Former Member
0 Kudos

But means that I will have to know all valuest to writte InList operator, what about if is coming a new one? It will be necessary to change the variable values, isn't it?


former_member210032
Active Participant
0 Kudos

Hi Oscar,



You can use Inlist operator for multiple vale..........




Regards

Mustafa

former_member210032
Active Participant
0 Kudos

Hi Oscar,

Create 2 variable

1.variable

Sum1=Sum([Revenue]) Where([City] InList("Lyon";"Paris"))

2.variable

Final_Sum=If [City]InList("Lyon";"Paris") Then ([Sum1]) Else [Revenue]

Apply "Final_Sum" variable in report

Regards

Mustafa

Former Member
0 Kudos

Thank you Mustafa for your help

I expose a little example of my needed, in reality we have thousands of registers as city. Your solution will work perfectly in the example but not in my customer report.