Multiple IF Condition

[MK9885]

Hi,

I've been trying to implement multiple IF condition.

I've achieved it when I had 2 if's now I need to add 3rd if which can also be done but the problem is it's range based.

Ex:

if($(v123)>=1000000,'A',

if($(v123)<=100000,'B'))

Now I want the result if 123 is >1000000 as A

If 123 is < 100000 as B

and if 123 is in between 1 and 99999 as C

Basically it is in between a particular number

Ex:between 1 to 100 is A, between 100 to 1000 is B and greater than or equal to 10000 is C

Thanks.

@

stalwar1‌

vinieme12‌

[MK9885]

I agree with all those suggestions and thanks everyone for that but it doesn't seem to fit my condition.

Cus of the obvious reason.

Qlikview is taking <10000000 as M as the next condition is also <10000000 but i'm using /100000 as K

Which still makes the 3rd condition invalid.

[sunny_talwar]

That is why I asked you to replicate the issue for us because there is no way in the world we would know why this isn't working for you.

Probably my last response before you can offer more.

Best,

Sunny

[Anonymous]

Using your original example, this should work.  Which I think Sunny first gave you.  If it doesn't something else is going on.

if($(v123)>=1000000,'A',

if($(v123)<=100000,'B','C'))

[MK9885]

I've create a small file for an example.

Not sure if it will serve the purpose.

Data looks like below 

Dim	Sales
A	$204,916.67
A	$5,794,201.90
A	$990,643.15
A	$672,527.73
A	$26,772.60
A	$6,203.34
A	$392,511.80
A	$0.00
A	$75,053.65
A	$112,744.11
A	$0.00
A	$2,047.68
A	$9,980.32
A	$2,158.92
A	$7,058,886.57
A	$5,936,535.52
A	$193,376.14
A	$86,237.73
A	$60,579.77
A	$274,158.91
A	$6,046.05
A	$2,242,319.29
A	$9,095,797.75
A	$9,212,228.72
A	$8,077,920.73
A	$7,795,556,741.61
A	$41,977,166.42
A	$357,587,787.20

I'm looking for

If A is >1000000000 then Billion

If A is <1000000000 then Million

and

If A is in between 10000000 and 1000 then Thousands.

[sunny_talwar · Accepted solution]

Here you go....

I added a fourth condition where if the number is less than 1000, it would be like '$#,##0.0'

Expression:

=If(Sales >= 1000000000, Num(Sales/1000000000, '$#,##0.0 B'),

  If(Sales >= 1000000, Num(Sales/1000000, '$#,##0.0 M'),

  If(Sales >= 1000, Num(Sales/1000, '$#,##0.0 K'), Num(Sales, '$#,##0.0'))))

[MK9885]

Thank a lot stalwar1‌

I wondered why I didn't upload the sample before to avoid all this confusion and frustration.

Thanks everyone for their suggestions.

[sunny_talwar]

I am glad that you finally got your solution, but I really would like to point out few things to you...

1) Try to save everyone's time by sharing a sample. I understand data is confidential, but you can always share dummy data.

2) Whenever possible, share a qvw over an Excel. It just take extra 2 minutes to create that qvw from the Excel and saves us those 2 minutes of time.

3) I would suggest marking lot of Helpful Responses to reward those people who tried to help you because I think there are at least 10 responses here which were correct (my current expression was no different from my earlier responses and not sure how this one worked when those did not work)

Don't get me wrong brother, we are here to help, but make it easier for us to help you.

Also, read these

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Thank You,

Sunny

[MK9885]

I fully understand and should have uploaded a sample along with the post.

I'll follow that from next time when the question becomes confusing or complicated.

And it worked as you've added an extra condition at the end of expression, before that I literally tried everything from here.

Thanks.