Skip to Content
0

Sum function retrieving duplicate rows. How to remove?

Jan 10, 2017 at 01:34 PM

82

avatar image

capture1.pngcapture3.pngWhen I directly drag and drop the object I am getting less value but when I create new cell with sum function for the same object, then I am getting wrong result

capture1.png (11.0 kB)
capture3.png (10.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

AMIT KUMAR
Jan 10, 2017 at 01:39 PM
0

This is happening because you have duplicate values.

Right click on the table->Format table->checked the checkbox "Avoid duplicate row aggregation" and you will get the total sum 13320.

to avoid this edit the webi query->Properties->uncheck the checkbox "retrieve duplicate rows" and see.

Show 7 Share
10 |10000 characters needed characters left characters exceeded

I have already tried your solutions.When I do this I am getting less value in DB and more value in Report.

The above object is Dimension. Is it necessary to make it as Measure?

0

what is the total coming when you checked the option at webi table level?

what is the total coming when you are unchecking the checkbox at webi query level?

0

If I unchecked that option am getting 510 and when enabling that option am getting 13320

untitled.png (64.3 kB)
0

what is the value in the database? if it's 510 then edit the webi query->Query properties and uncheck the option "retrieve duplicate rows" and see.

create the measure object and try.might be some values are not duplicate but because of same values these are appearing as duplicate.

0

In Database we have 510. So unchecked the "retrieve duplicate rows" and got correct data as per DB. but when I tried to sum those data in a individual cell then again I am getting wrong total 13320. I need to get 510 even in individual cell. If you refer my attachment I have used same formula but the result is different.

capture1.png (11.0 kB)
capture3.png (10.8 kB)
0

have you unchecked from webi query?

untitled.png (38.6 kB)
0
Show more comments
Sateesh Kumar Bukkisham Jan 11, 2017 at 11:58 AM
0

Hi Nithya ,

It appears your issue is because of calculation context .

Go through https://michaelwelter.wordpress.com/2011/07/31/removing-the-confusion-from-calculation-contexts/

Add required dimensions to the formula .

Regards

Sateesh

Share
10 |10000 characters needed characters left characters exceeded