Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
leaaamon
Contributor
Contributor

How take change negative values as 0 in editor

Hi,

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

Could any help me ?

Many thanks,

Lea

1 Solution

Accepted Solutions
sunny_talwar

May be this

RangeMax(FieldName, 0) as FieldName

View solution in original post

12 Replies
sunny_talwar

May be this

RangeMax(FieldName, 0) as FieldName

View solution in original post

leaaamon
Contributor
Contributor
Author

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
Champion III
Champion III

May be try like

= IF(Sales < 0, 0, Sales)

leaaamon
Contributor
Contributor
Author

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
Not applicable

just use the function Sunny suggested.

sunny_talwar

Where are you checking for negatives in this?

leaaamon
Contributor
Contributor
Author

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
Champion III
Champion III

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.