Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Sounds reasonably simple, i´ll give it a try. Thanks
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?
No one?