10 Replies Latest reply on Nov 13, 2019 3:54 AM by Ashfaque Shaikh

    Condition statement

    Ashfaque Shaikh

      Hello All,

       

      Need help in creating calculated field

       

      If Bonus program=MILLSADJ then I dont need column O_Segmentation and I_segment_code.

       

      Can you please  Help

       

       

      Thanks,

      Ash

        • 1. Re: Condition statement
          Jim Dehner

          Hi

          we really like to see the twbx workbook so we can tell what is data and what is calculations  but I believe that you have data in the 2 fields referenced as O and I segment and in the case where bonus program is some value you want the O ans I segment to be null  and in all other incidents you want the data value?   you would do that with a calculation    like    if [bonus_program] <>' MILLADJ'  then [O_Segmenta]  end    and a similar expression for I segment

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Condition statement
            Branden Kornell

            Jim's right; a workbook would be helpful.

             

            What do you mean by "don't need column"? Are you just looking to make the entries in those columns blank, or to remove them entirely from the table?

            • 3. Re: Condition statement
              Ashfaque Shaikh

              Hi Jim ,

               

              Thanks for reply but I want to drop both column( O_Segmentation and I_segment_code.) If Bonus program=MILLSADJ.

               

              Sorry but I cant share workbook.

               

               

              Regards,

              Ash

              • 4. Re: Condition statement
                Ashfaque Shaikh

                Hi Jim ,

                 

                Thanks for reply but I want to drop both column( O_Segmentation and I_segment_code.) If Bonus program=MILLSADJ.

                 

                Sorry but I cant share workbook.

                 

                 

                Regards,

                Ash

                • 5. Re: Condition statement
                  Ashfaque Shaikh

                  Yes Branden I want to remove both column when Bonus_program='MILLSADJ' for other bonus program  both column should be available.

                  • 6. Re: Condition statement
                    Soumitra Godbole

                    Hi Ashfaque,

                     

                        Please find a really cool and easy solution to your problem. Hope this is helpful and answers

                    your question. I have attached the twbx file and excel spreadsheet below.

                     

                        In my example I would like to see the names and city of all customers except those who live in

                    California (most of the customers are from there ). It uses the same logic as your question does

                     

                    Untitled.png

                     

                    I made up a dummy data-source using the data subset from Superstore sales and made it work

                    exactly like your question did  i.e. for a given condition, we make the data in the last 2 columns

                    vanish / disappear / not display (whatever you call it ).

                     

                    The trick was extremely easy and as I mentioned in one of my earlier posts "Looks can be deceptive"

                    (not exactly what I said but I meant something similar). In relation to this the visual I created is NOT

                    a Crosstab but a combination of a Cross tab and two Bar Charts (using a constant value or length of 1)

                    side by side (painted white).

                     

                    The 2 conditional fields are used as labels for these 2 bars and voila, you can add your own condition

                    as to how you want the data to show up or stay hidden based on a field value.

                     

                    In this case Conditional Field 1,    If  [State] <> "California" Then [City]  ELSE "" END

                    Similarly for Conditional Field 2 ,    If  [State] <> "California" Then [Customer Name]  ELSE "" END

                     

                    Here is a screenshot of the visual and apart from 2 calculated fields and 2 constant value fields (val = 1)

                    there is nothing else you need to do other than a little formatting (hiding unnecessary lines etc).

                     

                    Visual.png

                     

                    Hope this was a fun, easy and interesting walk-through and answered your question . In case it is

                    the right answer, then please mark as helpful, correct answer. This way others will a similar question

                    will easily be able to find it. Thanks !


                    Sincerely,

                     

                    Soumitra

                    2 of 2 people found this helpful
                    • 7. Re: Condition statement
                      Branden Kornell

                      Very slick idea, Soumitra!

                      2 of 2 people found this helpful
                      • 8. Re: Condition statement
                        Soumitra Godbole

                        Thanks Brandon ! You seem to be doing great as well as I have come across and admired

                        your unique and elegant solutions.

                         

                        If I remember correctly, many moons ago we worked on a problem related to YoY % change

                        using LODs and only one continuous line. It seemed to be "Never Ending" (not Never land ),

                        Never mind that (Never say Never again ). I still crack up remembering your cool solution

                        with a humorous title "....Rocks". Anyway glad to meet again as those were fun times.

                         

                        Best wishes !

                         

                        Soumitra

                        1 of 1 people found this helpful
                        • 9. Re: Condition statement
                          Soumitra Godbole

                          Hi Ashfaque,

                           

                              I was doing a follow up to check if you went through the solution. In case it answers

                          your question, then please mark as helpful, correct answer to close the thread. This

                          way others with a similar question will easily be able to access this solution. Thanks !


                          Sincerely,

                           

                          Soumitra

                          1 of 1 people found this helpful
                          • 10. Re: Condition statement
                            Ashfaque Shaikh

                            Thank you all for help.

                             

                            Regards,

                            Ash