
1. Re: Can you fix IF formula ?
Andrew Watson Sep 14, 2016 5:53 PM (in response to Shesil Parmar)Alter your formula to this and see if you still have NULLs:
IF ([Total Amount]) <= 25 THEN '$1$25'
ELSEIF ([Total Amount] >=26 AND [Total Amount] <=50) THEN '$26$50'
ELSEIF ([Total Amount] >=51 and [Total Amount] <=75) THEN '$51$75'
ELSEIF ([Total Amount] >=76 and [Total Amount] <=100) THEN '$76$100'
ELSEIF ([Total Amount] >=101 and [Total Amount] <=125) THEN '$101$125'
ELSE 'Greater than 125'
END
If it works as expected you'll get a lot of 'Greater then 125'. If you think this shouldn't be returned please provide some data or a twbx file showing where it isn't returning the expected results.


3. Re: Can you fix IF formula ?
Andrew Watson Sep 14, 2016 6:02 PM (in response to Shesil Parmar)1 of 1 people found this helpfulI suspect I know what's going on, try this:
IF (SUM([Total Amount]) <= 25 THEN '$1$25'
ELSEIF SUM([Total Amount]) >=26 AND SUM([Total Amount]) <=50 THEN '$26$50'
ELSEIF SUM([Total Amount]) >=51 and SUM([Total Amount]) <=75 THEN '$51$75'
ELSEIF SUM([Total Amount]) >=76 and SUM([Total Amount]) <=100 THEN '$76$100'
ELSEIF SUM([Total Amount]) >=101 and SUM([Total Amount]) <=125 THEN '$101$125'
ELSE 'Greater than 125'
END
If it doesn't work please provide some data or a twbx  a screenshot of the result isn't that useful to help troubleshoot

4. Re: Can you fix IF formula ?
Shesil Parmar Sep 14, 2016 6:13 PM (in response to Andrew Watson)so i was creating a demo for you and the formula works there !!! when i try same formula in my original book than it only gives first two and last conditions and rest are missing !
just checked the format of the column, its currency , so i have no idea why it would not work in main book

5. Re: Can you fix IF formula ?
Deepak Rai Sep 14, 2016 7:46 PM (in response to Shesil Parmar)Hi Shesil,
Try this.
Thanks
Deepak
IF ([Total Amount]) <= 25 THEN '$1$25'
ELSEIF ([Total Amount]) <=50 THEN '$26$50'
ELSEIF ([Total Amount]) <=75 THEN '$51$75'
ELSEIF ([Total Amount]) <=100 THEN '$76$100'
ELSEIF ([Total Amount]) >=101 THEN '$101$125'
END

6. Re: Can you fix IF formula ?
John Brink Sep 14, 2016 8:30 PM (in response to Shesil Parmar)Formula is good it appears. It worked for me. I tried it on the sample  EU superstore dataset. I replaced your [Total Amount] with [Sales]
IF ([Sales]) <= 25 THEN '$1$25'
ELSEIF ([Sales]>=26 AND [Sales]<=50) THEN '$26$50'
ELSEIF ([Sales]>=51 and [Sales]<=75) THEN '$51$75'
ELSEIF ([Sales]>=76 and [Sales]<=100) THEN '$76$100'
ELSEIF ([Sales]>=101 and [Sales]<=125) THEN '$101$125'
END


8. Re: Can you fix IF formula ?
Shesil Parmar Sep 14, 2016 8:35 PM (in response to John Brink)Hi John,
yes thats right, when i try on sample data it works fine, but on my main workbook, i had a look in format, its in Currency format, so i m not sure why it doesn't work there !
any other reason you can think of ?

9. Re: Can you fix IF formula ?
John Brink Sep 14, 2016 8:52 PM (in response to Shesil Parmar)This is sales number format I am using. I hate to state the obvious and I'm not insulting your intelligence, but are you sure your data set has values of between 50 and 125? I mean if you are getting nulls that may be the case. Try changing the actual numbers and see what you get. I'd be curious.
Try this.
IF ([Total Amount]) <= 25 THEN '$1$25'
ELSEIF ([Total Amount] >=26 AND [Total Amount] <=50) THEN '$26$50'
ELSEIF ([Total Amount] >=51 and [Total Amount] <=750) THEN '$51$750'
ELSEIF ([Total Amount] >=751 and [Total Amount] <=7000) THEN '$750$7000'
ELSEIF ([Total Amount] >=7001 THEN '$7001'
END

10. Re: Can you fix IF formula ?
Shesil Parmar Sep 14, 2016 9:08 PM (in response to John Brink)there are around 30million + records and value varies from 1 to 8000, so i have no clue.
i have tried so many different ways but only shows first 2 conditions on main book and when i try on sample data it works absolutely fine. i m 100% certain that formula is correct otherwise it wouldn't work on sample data. but i dont know what, the format is all good , had a look in actual data, it consist all different values.

11. Re: Can you fix IF formula ?
John Brink Sep 14, 2016 11:04 PM (in response to Shesil Parmar)Shesil, yes for now we know the formula is correct....at least for a diff dataset.
All I am saying is that formula would be showing the correct behavior of only showing the first two IF the dataset did not have those values. Look at the disproportionality here too. Pull an avg for total amount out of those 30 million records. Is it around 2040?
But since you are assuring me that there are values in the dataset between 50 and 125 I won't belabor the point anymore
Just to experiement (I assume you are open to other ideas), can we try the formula on a different measure besides [total amount] in that dataset?
I'm curious also is [total amount] a native or calculated field. If calculated what is it.
JPB

12. Re: Can you fix IF formula ?
Shesil Parmar Sep 14, 2016 10:18 PM (in response to Shesil Parmar)sorry guys.. my bad.. or the person who created data.
so when i drilled down to actual data, (took me very long as there were millions of records) found out data were incossistance, need to fix that first so i m sure all the formulas above works fine. apologies