Duplicates values Elimination using context operators

[Former Member]

Hi All,

I am facing the below issue.

IC_CODE      IC_C_CODE   LN           AMOUNT     TN_NO

21                    10                     234          1000              101

21                    10                     234          1000               102

21                    10                     232          500                 102

21                    11                     230           2000              103

21                    11                     230           2000               104

my o/p should look like this.

IC_CODE     IC_C_CODE   AMOUNT

21                    10                     2500

21                    11                    4000

I have created a variable TOTAL_SUM -> =Sum(Amount ForAll([LN]) In(IC_C_Code])) but i am getting multivalue error because of duplicate LN number.

Any help is grealty appreciated.

Thanks in Advance!!

Thanks,

Reacha

[former_member193452]

The Universe is semantically dynamic by design.

The reporting tool will roll up the aggregated measure objects based on the dimension objects selected in the Query and on the Report.

You can extend the reporting values using on report variables with calculation contexts.

a context as described in the Using Functions Formaulas and calculations in Web Intelligence User guide.

such as Max ([Sales Revenue] ForEach ([Quarter])) In ([Year])

Sum([AMOUNT] For Each ([IC_CODE] ) In [(IC_C_CODE])) More information on using report variables is herehttp://scn.sap.com/docs/DOC-7819

[jeff_crisp]

Ok, when you click on the field, in the measure information area, next to the "Data Type" there is a "Projection Function:".  Is the drop down set to "Sum"?

If not, then set that to Sum and then re-try the query.

[Former Member]

yes, but i am not using that object because it is summing the duplicate amounts for LN.

Sorry ..my o/p should like this.

IC_CODE     IC_C_CODE   AMOUNT

21                    10                     1500 (Instead of 2500)

21                    11                   2000

[jeff_crisp]

Instead of creating a variable, try turning your "Amount" field into a measure with summing aggregation.

To do this, right-click on your "Amount" object (dimension) in IDT.  Then hover over "Turn into Measure with Aggregation Function".  Then select "Sum".

Retry your query, and I believe you will get the result you are expecting.

[Former Member]

It is already a measure object in the universe.