I have a dimension and fact in OBIEE that report like this:
Ideally, I want to make an analysis that looks like this:
I believe that the two fact columns should be able to be expressed like this:
CASE WHEN LEFT("ColumnA",4) = 'Non-' THEN “Fact Amount” ELSE 0 END
CASE WHEN LEFT("ColumnA",4) = 'Non-' THEN 0 ELSE “Fact Amount” END
When I create these two columns, I get this analysis, as expected:
I then attempted to set the dimension column to values that group rows with their ‘Non-’ counterparts like this:
REPLACE("ColumnA", 'Non-', '')
However, the case when fact columns all defaulted to summing in the “Yes amount” like this:
So it seems like the Replace adjustment to the dimension doesn't allow the facts to aggregate correctly. I attempted to use a listagg function to have both the yes dimension and its Non- counterpart to be described in the same row, but the query returns an error:
EVALUATE_AGGR('LISTAGG(%1, ", ") WITHIN GROUP (ORDER BY %1)' AS VARCHAR(1000), "ColumnA")
Is there a way to rewrite the fact column or use a GROUP BY in the advanced tab to compile the listagg function and get the desired effect?