Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

[#1]

Hi there,

I need to highlight whether data captured is over a certain threshold.

Ideally I want to create an IF statement that if the value is >xx it shows the value captured and if not, it shows 'N/A'.

This is what I've tried.... IF([Trade Value] >(XX),[Trade Value],'N/A') as [Trade >xx]

It seems to load ok when reloading the data but the results are unexpected as I only receive N/A's when the data set has values greater than the threshold.

any help will earn you a coffee and a doughnut

[swuehl]

Well, what is (XX)? Where does the threshold come from?

If it's a variable called XX, you probably need to write:

IF([Trade Value] > $(XX),[Trade Value],'N/A') as [Trade >xx]

If it's a field, is this field located in the same table?

[its_anandrjs]

You have to write a code like as i understand your requirement

IF([Trade Value] >(XX),[Trade Value],'N/A') as [Trade >xx]

Eg:-

IF( [Trade Value] > 200, [Trade Value] , 'N/A' ) as [Trade >xx]

Regards

[#4]

Try using the about statements in the scripts than directly in charts, if used at chart levels

[its_anandrjs]

If you pass this threshold value to any variable like XX then use script like

Eg:-

Let XX = YourThresoldvalue;

Load

[Trade Value],

IF([Trade Value] > $(XX),[Trade Value],'N/A') as [Trade >xx]

From Location;

Regards

[#6]

wow these are some swift responses - thank you

SWUEHL - the xx's are 75000 so the statement i've (poorly) written is as follows;

IF([Trade Value] > 75000,[Trade Value],'N/A' as [Trade Value >75000]

and yes it is a field (within a SharePoint library)

Anand - thanks for the response, I have tried removing the brackets around the 75000 but it still just brings back a - rather than the figure in the table
I will give the the second one a go just now

Kiruthiga - thank you I will try this also (just need to learn about the about statements now

thanks to you all

[its_anandrjs]

I explain you how it works Suppose a field [Trade Value] contains value

Eg:-

[Trade Value]

100000

75000

12000

120000

450

18524

158623

Load

[Trade Value],

IF([Trade Value] > 75000,[Trade Value],'N/A' as [Trade Value >75000]

From Location;

After Load of your statement you get data in Field [Trade Value >75000] like

Trade Value	Trade Value >75000
450	N/A
12000	N/A
18524	N/A
75000	N/A
100000	100000
120000	120000
158623	158623

Like this you are doing if so please check

Regards

[swuehl]

I have not fully understood if 75000 is a literal or a field. Could you elaborate this a little, by posting your complete load statement?

[#9]

here it is;

Load

[FundsInformation/Trade Value] as [Notional Trade Value],
IF([Notional Trade Value] >75000000,[Notional Trade Value],'N/a') as [Notional Trade >£75m]

FROM [\\www.avivaworld.com@...........] (XmlSimple, Table is [myFields]);

many thanks for taking the time and effort - it really is appreciated

[swuehl]

Ah, you can't use the aliased field name in the same LOAD e.g. in a condition.

I think that you even should got a script error when trying so.

Instead, try

Load

[FundsInformation/Trade Value] as [Notional Trade Value],
IF([FundsInformation/Trade Value] >75000000,[FundsInformation/Trade Value],'N/a') as [Notional Trade >£75m]

FROM [\\www.avivaworld.com@...........] (XmlSimple, Table is [myFields]);

or use a preceding LOAD:

LOAD

     *,

     IF([Notional Trade Value] >75000000,[Notional Trade Value],'N/a') as [Notional Trade >£75m];

LOAD

[FundsInformation/Trade Value] as [Notional Trade Value]

...

FROM [\\www.avivaworld.com@...........] (XmlSimple, Table is [myFields]);

And make sure that your [FundsInformation/Trade Value] field is interpreted as a number, not a text (e.g. when containing currency symbols).