12 Replies Latest reply on Sep 14, 2016 11:04 PM by John Brink

    Can you fix IF formula ?

    Shesil Parmar

      hi guys,

      this if formula driving me crazy since morning, its pretty simple but still not able to extract what i m after.

      so i have few millions records and want to group it by amount, i have got below formula but it only shows first two conditions (ie 1-25 and 26-50 and rest shows as Null)

      i have tried few methods but not luck. any suggestion ?

       

      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'

      END

        • 1. Re: Can you fix IF formula ?
          Andrew Watson

          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.

          • 2. Re: Can you fix IF formula ?
            Shesil Parmar

            Thanks Andrew for prompt response,

            only getting first two and last condition , the rest are missing

             

            • 3. Re: Can you fix IF formula ?
              Andrew Watson

              I 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

              1 of 1 people found this helpful
              • 4. Re: Can you fix IF formula ?
                Shesil Parmar

                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

                  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

                    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

                     

                     

                    • 7. Re: Can you fix IF formula ?
                      Shesil Parmar

                      Thanks Deepak, still no luck, it works when i try on test data , see below based on your formula

                       

                      • 8. Re: Can you fix IF formula ?
                        Shesil Parmar

                        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

                          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

                            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

                              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 20-40?

                               

                               

                              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

                                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