8 Replies Latest reply on Feb 16, 2018 12:30 PM by Michael Hesser

    Convert Excel If condition into Tableau condition

    Mithu Bhudolia

      Hi All,

       

      I was trying to convert this Excel Formula into Tableau, but its not working, just for your reference my work around :-

       

      Tableau work around ( not working)

      IF ATTR([Metric Name]) = 'View and (SUM([PRESENT]) - SUM([PAST]))/SUM([PAST])  > 0

      THEN

          IF ATTR([Metric Name]) = 'Details' and (SUM([PRESENT]) - SUM([PAST]))/SUM([PAST])  < 0

          THEN

             // IF ATTR([Metric Name]) = 'Total IVMS' and (SUM([PRESENT]) - SUM([PAST]))/SUM([PAST])  > 0

             //     THEN

                      ATTR([Brand]) + " has seen enjoyed the best possible result in that " + ATTR([Brand]) + " saw the CVR increase by " +

                      (IF ATTR([Metric Name]) = 'Conversion Rate' THEN STR((SUM([PRESENT]) - SUM([PAST]))/SUM([PAST])) END) +

                      " in parallel to an increase in traffic of " //+ 

      ELSE "NO Data"

      END END //END

      -------------------------------------------------------------------------------------------------------------------------------------------

       

       

      Problem:- ( required solution for this problem)

      =IFERROR(IF(AND(M24>0,M28<0,M26<0),CONCATENATE("While Total for ",UPPER(D4)," seen on its own has improved in absolute % by ",TEXT(J24,"0.00%"),", likely faciliated by a trop in traffic of ",TEXT(M28,"0.00%"),", however ",UPPER(D4),"'s Total vs competitors has fallen in relative terms by ",TEXT(M26,"0.00%"),"."),

       

      IF(AND(M24>0,M28<0,M26>0),CONCATENATE("Total for ",UPPER(D4)," seen on its own has improved by ",TEXT(J24,"0.00%")," likely faciliated by a trop in traffic of ",TEXT(M28,"0.00"),". Further ",UPPER(D4),"' Total vs competitors has improved in relative terms by ",TEXT(M26,"0.00%")),

       

      IF(AND(M24>0,M28>0,M26<0),CONCATENATE("While Total for ",UPPER(D4)," seen on its own has improved by ",TEXT(J24,"0.00%")," despite ",UPPER(D4)," seeing a traffic increase of ",TEXT(M28,"0.00%"),"(!), the ",UPPER(D4)," Total vs competitors has fallen in relative terms by ",TEXT(M26,"0.00%"),"."),

       

      IF(AND(M24<0,M28>0,M26<0),CONCATENATE("Total for ",UPPER(D4)," has detoriated in two ways, one versus ",TEXT(D5,"General")," and second, in relative terms versus it competitors (by ",TEXT(M26,"0.00%"),"). This could be driven by ",UPPER(D4)," enjoying an increase in glance view volumes (here: ",TEXT(M28,"0.00%"),") or the result of conversion rate improvement efforts by competitors. Another explanation could be operation issues at ",UPPER(D4)," - please have a look at the RepOOs- and LBB- Metrics!"),

       

      IF(AND(M24<0,M28<0,M26<0),CONCATENATE("Total for Philips has detoriated in two ways, once versus ",TEXT(D5,"General")," and second, in relative terms versus it competitors (by ",TEXT(M26,"0.00%"),"). This situation is made worse by the fact that ",UPPER(D4)," has simultanously seen a decrease in glance view volumes (here: ",TEXT(M28,"0.00%"),". While there is slim chance competitors's conversion rate improvement efforts were extremely effective, it seems relatively more likely that there have been operational issues at  ",UPPER(D4)," Definetely have a close look at the RepOOs- and LBB- Metrics!"),

       

      IF(AND(M24<0,M28<0,M26>0),CONCATENATE("While ",UPPER(D4)," has seen its total drop by ",TEXT(M24,"0.00%")," despite traffic dropping ",TEXT(M28,"0.00%")," (!), ",UPPER(D4)," has seen a relative improvement of Total by ",TEXT(M26,"0.00%")," vs. Competitors. This implies competitors were either not able to 'convert' increased traffic volume to their product detail pages OR faced issues of their own with preserving their Total at constand traffic volumes"),

       

      IF(AND(M24>0,M28>0,M26>0),CONCATENATE(UPPER(D4)," has seen enjoyed the best possible result in that ",UPPER(D4)," saw the Total increase by ",TEXT(M24,"0.00%")," in parallel to an increase in traffic of ",TEXT(M28,"0.00%")," AND, further, ",UPPER(D4)," even accomplished an increase of (",TEXT(M26,"0.00%")," in the Total relative to the Total of direct competitors."),

       

      ""))))))), "")

       

       

      *-- M24 = View

      *-- M28 = Details

      *-- M26 = Total

      *-- D4 = Name

      *-- J24 = Price

       

      Please try to help me its urgent.

       

      Thanks

      Rahul

        • 1. Re: Convert Excel If condition into Tableau condition
          Jyothisree Rayagiri

          Can you please attach your workbook where you have your if condition and we may try editing it.

          • 2. Re: Convert Excel If condition into Tableau condition
            Mithu Bhudolia

            Workbook is very heavy , not able to attach here . but if you can try to replicate this excel formula in tableau, it will be helpful.

            • 3. Re: Convert Excel If condition into Tableau condition
              Jyothisree Rayagiri

              Can you build a simple sample dataset in excel and outline what you are trying to achieve?

              It is difficult to understand your data and output just from formulas

              • 4. Re: Convert Excel If condition into Tableau condition
                Mithu Bhudolia

                Just for your reference i have attached a sample work book 9with sample data)  and my required solution in comment section in excel.

                 

                Example:-

                 

                E4- "CR"

                E5 - "GV"

                E6 - "GY - IYA"

                 

                EXCEL OUTPUT (Comment populating dynamically) with this formula -  "   IF(AND(E4>0,E5>0,E6>0),CONCATENATE("'CR' is less  than 'GV' ", E5,"  But its greater then 'GY - IYA' ", E6) "

                 

                 

                 

                Tableau Sample Output :- ( Required comment section dynamically )

                • 5. Re: Convert Excel If condition into Tableau condition
                  Jyothisree Rayagiri

                  Do you want compare always CR with GV and GY-IYA ?

                  • 6. Re: Convert Excel If condition into Tableau condition
                    Mithu Bhudolia

                    Hi , basicallythis is the case:-

                     

                    and will have to write different formula for CR - IYA , CR IVMS etc  ( so if we are able to crack one, we can handles others also) , please have  a look , its super urgent .

                    i tried some example , let em show you but those are not working :- please check if you can :-

                     

                    CALC1:- ( Not working)

                    CALc 2 (NOT WORKING)

                    CALC 3 (NOT Working)

                     

                    IF ATTR([Metric Name]) = 'CR' AND (SUM([PRESENT]) - SUM([PAST]))/SUM([PAST])  > 0 

                            THEN                   

                                (IF ATTR([Metric Name]) = 'CR IVMS ' AND (SUM([PRESENT]) - SUM([PAST]))/SUM([PAST])  > 0 

                                    THEN

                                        (IF ATTR([Metric Name]) = 'GV ' AND (SUM([PRESENT]) - SUM([PAST]))/SUM([PAST])  > 0 

                                            THEN                       

                                                ATTR([Brand]) + " has seen enjoyed the best possible result in that " + ATTR([Brand]) + " saw the CVR increase by " +

                                                (IF ATTR([Metric Name]) = 'Conversion Rate' THEN STR( ROUND((SUM([PRESENT]) - SUM([PAST]))/SUM([PAST]),1)) END) +

                                                " in parallel to an increase in traffic of " +

                     

                                                (IF ATTR([Metric Name]) = 'GV' THEN STR((SUM(C.MONTH) - SUM(P.MONTH))/SUM(P.MONTH)END) +

                                                "AND, further " + ATTR([Metric Name]) + " even accomplished an increase of " +

                     

                                                (IF ATTR([Metric Name]) = 'CR IVMS' THEN STR((SUM(P.MONTH) - SUM(P.MONTH))/SUM(P.MONTH))END) +

                                                " in the CVR relative to the CVR of direct competitors. "

                                        END

                                END

                    END   

                     

                     

                    Please try,  It will be helpfull

                     

                    Thanks

                    Mithu

                    • 8. Re: Convert Excel If condition into Tableau condition
                      Michael Hesser

                      Hi Mithu...

                      I made two calcs (one for GV% and GV-IYA%):

                       

                      GV%

                      round({fixed:sum(iif([Metric name]="GV",[Change %],0))},2)

                       

                      GV-IYA%

                      round({fixed:sum(iif([Metric name]="GV-IYA",[Change %],0))},2)

                       

                      Then an extended if to handle most of the cases:

                       

                      Comment_MH

                      if SUM([Change %])< avg([GV%]) AND SUM([Change %]) < avg([GV-IYA%]) THEN

                           max([Metric name])+" is less than 'GV' "+str(avg([GV%]))+" and 'GV-IYA' "+str(avg([GV-IYA%]))

                      elseif SUM([Change %])< avg([GV%]) AND SUM([Change %]) > avg([GV-IYA%]) THEN

                           max([Metric name])+" is less than 'GV' "+str(avg([GV%]))+" and greater than 'GV-IYA' "+str(avg([GV-IYA%]))

                      elseif SUM([Change %])> avg([GV%]) AND SUM([Change %]) < avg([GV-IYA%]) THEN

                           max([Metric name])+" is greater than than 'GV' "+str(avg([GV%]))+" and less than 'GV-IYA' "+str(avg([GV-IYA%]))

                      elseif SUM([Change %])> avg([GV%]) AND SUM([Change %]) > avg([GV-IYA%]) THEN

                           max([Metric name])+" is greater than 'GV' "+str(avg([GV%]))+" and 'GV-IYA' "+str(avg([GV-IYA%]))

                      END

                       

                      I did not create conditions where the values might be equal, but you can use the same format and enter these.

                      My result:

                      The blank lines are where the values are equal and were not handled in my initial coding.

                       

                      I think, on reflection, and better way to handle this is to make TWO string results:

                      The first would determine the relationship to GV (>,=,<)

                      The second would determine the relationship to GV-IYA.

                      A third calc would concatenate these.

                       

                      Let me know if it works!