Changing a field value from -1 to 'Y'

[samantha92]

Hi,

I am working with cost information for clients and each individual client needs to have a spend banding.

I have created the following expression to show the spend banding of anything less than £250.00 - It is currently showing as 0 and -1 figures and  I am unsure of how to change the figure to 'Y' rather than -1.

sum(TOTAL <[SWIFT ID]>if(len(SCHEDULESALTWEEKLYCOST)>0, SCHEDULESALTWEEKLYCOST,
if(len(SCHEDULESALTWEEKLYCOST)=0,STDWEEKCOST))) <='250.00'

Can anyone help? I have tried changing the values and I am having no luck. I am wondering if this is something to do with how I have created my expression and maybe that is wrong?

Much appreciated.

Sam

[daveamz · Accepted solution]

Hi Samantha,

Try to enclose everything into another IF statement:

If(sum(TOTAL <[SWIFT ID]>if(len(SCHEDULESALTWEEKLYCOST)>0, SCHEDULESALTWEEKLYCOST,
if(len(SCHEDULESALTWEEKLYCOST)=0,STDWEEKCOST))) <='250.00' , 'Y', 'N')

Few notes:

- you don't need quotes when comparing to numbers <= '250.00'. In this case, Qlik is doing a string comparison instead of number comparison.

- I suppose the SCHEDULESALTWEEKLYCOST field is sometimes null since you use that if test. Have a look at Alt() function. It is faster and easier to read (e.g If(sum(TOTAL <[SWIFT ID]>  Alt(SCHEDULESALTWEEKLYCOST, STDWEEKCOST, 0) <= 250 , 'Y', 'N'))). If it doesn't work, consider to move the if test in the script to increase the front end performance (IF statements are pretty slow in expression)

Regards,

David

[samantha92]

Hi David,

That works a charm - thanks so much!

Really good advice with the Alt() function - I need to read up on enhancing my expression for performance.

Thank you again

Sam