How take change negative values as 0 in editor

[Anonymous]

Hi,

I would like to change the negatives value of one dimension as 0 in the script.

Could any help me ?

Many thanks,

Lea

[sunny_talwar · Accepted solution]

May be this

RangeMax(FieldName, 0) as FieldName

[Anonymous]

I would like to make all the negative value as 0.

eg.

Item Name, Sales

A, -3

B, 3

C, -1

D, 0

The data should be:

Item Name, Sales

A, 0

B, 3

C, 0

D, 0

[vishsaggi]

May be try like

= IF(Sales < 0, 0, Sales)

[Anonymous]

Could I use this directly in the scrip or should I add it to the calculated dimension:

=aggr(

if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.1), 10,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.2), 9,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.3), 8,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.4), 7,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.5), 6,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.6), 5,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.7), 4,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.8), 3,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.9), 2,1))))))))),[Item Description])

[Anonymous]

just use the function Sunny suggested.

[sunny_talwar]

Where are you checking for negatives in this?

[Anonymous]

I want to remove change negatives to 0 in Net Sale Amount

[sunny_talwar]

May be this

=aggr(

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.1), 10,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.2), 9,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.3), 8,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.4), 7,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.5), 6,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.6), 5,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.7), 4,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.8), 3,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.9), 2,1))))))))),[Item Description])

[vishsaggi]

The best preferred way is to use Sunny's RangeMax in your script. Like where ever you have your Net Sales Amount in your script. Like

LOAD Field1, Field2,....,

           RangeMax([Net Sales Amount], 0) AS [Net Sales Amount]

FROM yoursourcetable;

Then you can use your aggr() calculated dim as is without any changes. If this is not what you looking for please elaborate.