How to Ignore negative values in Summing up a measure value

building reports webintelligence
1

Hi Guys,

We have a requirement of not taking negative values into consideration while summing up a measure values.

We get some test readings for a Batch every hour sometimes they are -ve. User wants to see those value against the date something like following:-

BatchNo. Date Reading
XXXXX 07/27/2014 1613.00

The way values I get from Database is something like that:-

Batch No. Date HrValue Reading
xxxxx 07/27/2014 00:01:00 29.00
xxxxx 07/27/2014 00:02:00 -45.00
so on…
xxxxx 07/27/2014 00:59:00 65.00

1613 is the sum of only positive values. How can I do that:-

I tried following:-

=Sum([Reading]) where ([Reading] >0). But it does not help me.
Can anyone please help.

Regards
Prashant

p.mangal (BOB member since 2014-01-29)

2

Hi,

Try a formula like this one:

=Sum( If [Reading] > 0 Then [Reading] )

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

This did not help. The number format of the measure is number but it still says wrong data type for SUM.

p.mangal (BOB member since 2014-01-29)

4

Hi

Check this.

http://scn.sap.com/thread/3226556

Judia

judia (BOB member since 2008-06-24)

5

filter off negative values and then sum them. or maybe create a variable , which will return a 1 if positive or 0 if negative

=if[object]>0 then 1 else 0

then =sum([object] where([variable]=1)

erik.stenson :us: (BOB member since 2012-07-30)

6

Thanks Judia,

I did go thru that link earlier but it did not help me. Its very strange though!

Regards

p.mangal (BOB member since 2014-01-29)

7

HI Mangal,

Try this, i got it worked.
Use this formula
=If[Measure column]>0 Then Sum([Measure column])
THis column will have just positive values and you can sum them finally.

Judia

judia (BOB member since 2008-06-24)