Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

kindly help out to write the expression on the below condition

HI All,

i am having the column as  below ,

 

Lesser 90Greater 110Amount
500600400
600700800
700800850
800900750

i need to write the expression

if the amount is fall under lesser 90 it should display <90%

if the amount is fall under Greater 110 it should display <110

Below is the expected Result:

Lesser 90Greater 110AmountVariation
500600400   <90%
600700800   >110%
700800850   >110
800900750   <90%

Please help me out how to write the expression for this ,Very urgent requirement !.

10 Replies
vvira1316
Specialist II
Specialist II

Try

=if([Amount] >= [Lesser 90],

if([Amount] <= [Greater 110], 'In Range', '>100%'), '<90%')

vishsaggi
Champion III
Champion III

Another way of doing this like

Comparison:
LOAD *, (Greater110 - Lesser90) + Amount AS TotalDiffAmt INLINE [
Lesser90, Greater110, Amount
500, 600, 400
600, 700, 800
700, 800, 850
800, 900, 750
]
;

Use straight table with

Dim: Lesser90, Greate90, Amount

Expr: = IF(TotalDiffAmt >= Greater110, '>110', '<90')

Anonymous
Not applicable
Author

i have used the below expression

=if([Amount]<= [Lesser 90],

if([Amount] >= [Greater 110], 'In Range', '>100%'), '<90%')

it is displaying value as <90 and >100% percent for all the value,

for example

lesser 90 Greater 110 amount

400 450 350

from above the amount is not present in Amount value ,and it should display 'N/A'

but it is displaying <90%

Kindly help me out

Anonymous
Not applicable
Author

it is the Fact Table and i dont need to disturb that table,please kindly let me know the alternative solution

d_pranskus
Partner - Creator III
Partner - Creator III

Hi

Try the following

=IF([Amount] < [Lesser 90], '<90', IF([Amount] <= [Greater 110], '<110', '>110'))

jonathandienst
Partner - Champion III
Partner - Champion III

Like this then:

=If(Len(TotalDiffAmt) = 0, 'N/A', If(TotalDiffAmt >= [Greater 110], '>110%', '<90%'))

Or

If(Len(TotalDiffAmt) = 0, 'N/A',

     If(TotalDiffAmt > [Greater 110], '>110%',

     If(TotalDiffAmt < [Lesser 90], '<90%',

     '90-110'))

)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anil_Babu_Samineni

Will you provide sample application to test? Will implement without Touch the script. If not, We can do within script

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
shiveshsingh
Master
Master

Try in script

T:LOAD * INLINE [

   Lesser 90, Greater 110, Amount

    500, 600, 400

    600, 700, 800

    700, 800, 850

    800, 900, 750

];

F:

load  [Lesser 90], [Greater 110], Amount,

if (Amount >[Greater 110],'>110%',if(Amount<[Lesser 90],'<90%')) as Variance

Resident T;

drop table T

crusader_
Partner - Specialist
Partner - Specialist

Hi,

You're not quite precise in your requirements... Looking into initial thread- there should be only <90% and >110% result values.

Then looking into discussion, as far as I understood there should be 4 different statements:

'<90%' if amount<[lesser 90]

'In Range' if (amount >=[lesser 90] and amount <= [greater 110])

'>110%' if (amount > [greater 110])

'N/A' if (amount is null or empty or not a number)

Assuming above try the following:

if(amount < [lesser 90], '<90%',

     if(amount < [greater 110], 'In Range',

          if(amount  > [greater 110], '>110%', 'N/A' )

     )

)

Hope this helps.

//Andrei