Finding shared values in different alternate states.

[Remco]

Hello all!

I have a table with many records that uses the 'Forecast' column to define to what set of data they belong. With alternate status I can show the difference counts, totals, averages etc. for each set. There are many different 'Forecasts'.

Now, lets imagine I select a Forecast 'X' in alternate state A and for state B the selection is on Forecast 'Y'

 

What I want is that in the table fore alternate state A there will be a dimension that splits up the results between values found only in state A and values found in both state A and state B. These values to search on are a concat of 2 fields called [CountryCode] and [IdContract].

Then, in the output table for state B, I want to see the opposite so, the same dimension that splits up the results for records (the same concat) found both in state A en B and on the other hand, records only found in B.

The 'Count' of A en B will be the same number. I imagine this should be solved by using IF in combination with an 'IN' or 'Match' function but I am not able to figure it out. Any suggestions?

Thanks! 

[Or]

You should be able to achieve this using set analysis operators ( + - / * ). For example:

Sum({<SetAnalysisForState1 * AnalysisForState2)>} ForecastAmount)

Should get only the cases that are in both sets (intersect). + would get both sets (union), - would get only records which appear in the first but not in the second, and / would return the XOR - values which appear in one set but not the other.

[Remco]

Sounds reasonably simple, i´ll give it a try. Thanks

[Remco]

It does not work out, it adds, subtracts etc. but it is not doing what I need. I need to investigate if the combination of CountryCode & IdContract exists in either state A and B. Distinguish with a dimension within the table and with the existing measure return the values.

So, it should create a dimension with an IF function that returns 'Only in A', 'Both in A & B' and 'Only in B'. In SQL I would do something like

Case when CountryCode + IdContract in (Select CountryCode + IdContract from B) then 'Both in A and B' Else 'Only in A' from A

for the second table that would be something like:

Case when CountryCode + IdContract in (Select CountryCode + IdContract from A) then 'Both in A and B' Else 'Only in B' from B

The question is, how can I create this dimension?

[Remco]

No one?