1 2 Previous Next 22 Replies Latest reply on Jul 30, 2018 10:48 AM by priyesh acharya

    multiple if then

    priyesh acharya

      Hi All,

       

      I have to create a calculate field based on satisfying a few critieria.The data is at an Order Id level

       

      Value in col X>0

      Value in col Y is Yes or No (Both yes and no satisfy this critieria)

       

      If the above 2 conditions are met then the output should be "A"

       

      The other condition is

      the value in col X = 0

      value in col Y is No

       

      If the above 2 conditions are met then the output should be "B"

       

      The biggest concern here is, there are order ids where there are multiple rows of this order id which are marked as Yes or No ( col X) in different rows. How do I identify such order ids and create a third output for them 'C'.

       

      Hope some one can help me with this. Please reach out o me if you need clarification.

        • 1. Re: multiple if then
          Tausif Kazi

          Hi Priyesh,

           

          Please try this calculation and let me know if it is helpful.

           

          If col X = 0  and col Y = "Yes" or Col Y = "No" then

          B

          Else

          C

          Endif.

           

          Regards,

          Tausif.

          • 2. Re: multiple if then
            Zhouyi Zhang

            Hi, Priyesh

             

            Please find my solution attached

             

            Below is the screenshot of result.

             

             

            Hope this helps

             

            ZZ

            1 of 1 people found this helpful
            • 3. Re: multiple if then
              Naveen B

              Hi Priyesh,

               

              Kindly check the below screenshot

               

               

              Is this way you want??

               

              if it helps kindly mark this answer as helpful/correct so that it will help others.

              1 of 1 people found this helpful
              • 4. Re: multiple if then
                priyesh acharya

                Thanks for looking into this Tausif but this is not what I'm looking for

                • 5. Re: multiple if then
                  priyesh acharya

                  Thanks Zhouyi for your help, this points me in the right direction

                  • 6. Re: multiple if then
                    priyesh acharya

                    Hi Naveen,

                    this solved 90% of my problem. However one point that this solution cant solve and its because I probably didnt explain my question properly earlier so here goes

                     

                    Case 1:

                    Col X>0 and Col Y is Yes or No - this output is correct

                     

                    Case2:

                    Col X=0 and ColY is No - this output is correct

                     

                    Case3:

                    ColX>0 and Col Y is No - this output is wrong ( possibly because the condition we have for Case 1 is for ColY being Yes or No, so it seems when the third condition is run, the output is calculating the records generated previously as well)

                     

                    Does this clarify my problem?

                     

                    Thank you for your prompt help.

                     

                    Regards,

                    Priyesh Acharya

                    1 of 1 people found this helpful
                    • 7. Re: multiple if then
                      priyesh acharya

                      Just to add here,

                       

                      There are order ids where col X>0 and col Y is Yes but this order id will have another row where col X>0 and col Y is No. This order id should be calculated in one bucket because the principal condition is for an order id to have atleast one row where col X>0 and Col Y as Yes.

                       

                      The order ids where colX >0 and col Y as No can have single or multiple rows satisfying this condition but that is considered part of a separate bucket.

                      • 8. Re: multiple if then
                        priyesh acharya

                        Hi All,

                         

                        Can someone please help me with the above question, I'm hoping I was able to explain the requirement properly.

                         

                        Please reach out to me with any pointer you may have for me or in case you need any clarifications.

                         

                        Regards,

                        Priyesh Acharya

                        • 9. Re: multiple if then
                          priyesh acharya

                          Hi All,

                          Any pointers here will be much appreciated.

                          Moderator, can you please assist.

                           

                          Regards,

                          Priyesh Acharya

                          • 10. Re: multiple if then
                            Zhouyi Zhang

                            Hi, Priyesh

                             

                            What's your issue with my solution, you haven't explained which part is not working.

                             

                            ZZ

                            • 11. Re: multiple if then
                              Jonathan Drummey

                              Please supply an updated data set with examples of all the conditions that you are evaluating for and it would be helpful to have an extra column with the "expected status result" that we can use to check against the calculations in Tableau.

                               

                              Jonathan

                              • 12. Re: multiple if then
                                priyesh acharya

                                Hi ZZ,

                                 

                                The solution you provided is not working because the logic I explained earlier was not completely clear.

                                 

                                The problem statement is as follows:

                                There are some records which have 3 columns, order id, col X and col Y

                                 

                                I need to create a calculated field, set, whatever is the best solution to display the count of order ids for the 3 conditions mentioned below

                                 

                                Condition1:

                                col X>0 and col Y="Yes"  - these order ids should be kept separate and not be part of further calculations

                                 

                                after this,there are 2 more conditions

                                Condition 2:

                                col X=0 ad Col Y ="No"

                                 

                                Condition3:

                                Col X>0 and Col Y="No"

                                 

                                Basically, I'm trying to segment the order ids into 3 unique buckets based on these 3 conditions.

                                 

                                The solution Naveen provided earlier was able to satisfy the first 2 conditions but it gave the wrong count for the 3rd. I believe this was because some order ids from the first condition were also included in this calculation which led to inflated count. This is why I believe we need to completely filter out the first set before working on the other 2 conditions.

                                 

                                Thank you for reaching out.

                                 

                                Regards,

                                Priyesh Acharya

                                • 13. Re: multiple if then
                                  Zhouyi Zhang

                                  Hi, Priyesh

                                   

                                  This is what I replied in my original reply, can you explain why the calculation 'Condition' is not meet your unique bucket requirement?

                                   

                                   

                                  ZZ

                                  • 14. Re: multiple if then
                                    priyesh acharya

                                    Hi Jonathan,

                                     

                                    Thank you for reaching out, hope the below helps.

                                     

                                    1 2 Previous Next