11 Replies Latest reply on Jun 7, 2018 1:56 PM by Bryce Larsen

    Issue with IF and THEN statement

    Rohin Mahesh

      I am a beginner with using Tableau. I had a quick question about why I am getting an error message. I unforunately cannot attach a workbook per company policy, but help with this statement would be very helpful:

       

      IF([Work Item Type] = 'Task')

      THEN MAX([Remaining Work])

      ELSE END

       

      I am getting an error saying: cannot mix aggregate and non aggregate functions.

       

      Is there another way to do what I am trying to do?

        • 1. Re: Issue with IF and THEN statement
          Bryce Larsen

          You just need to wrap [Work Item Type] instead of an aggregate function (min, max, attr). But it depends on what your viz is. If it's row level with Work Item Type listed then this will be fine.

           

          Otherwise you could look to do a LOD expression such as {fixed [Work Item Type]: MAX([Remaining Work])}, while adding in additional fields to fix on (group by) depending on your viz.

          • 2. Re: Issue with IF and THEN statement
            arvindgarg

            Try using ATTR function with work item type. Like below 

             

             

            IF( ATTR([Work Item Type]) = 'Task')

            THEN MAX([Remaining Work])

            ELSE ..........

            END

            • 3. Re: Issue with IF and THEN statement
              Rohin Mahesh

              Thank you for getting back. The result is not what I was expecting unfortunately. I essentially have these columns: ID, Work Item Type, Remaining Work and Effort.

               

              I want to get the maximum Remaining Work for when the Work Item is Task. I want to be able to use the ID to find out if the Work Item Type is Task, what is the Maximum Remaining Work.

               

              I then want a visualization with Remaining Work and Effort as the X and Y axis variables. I then wanted to data to be filled with ID. When I hover over the ID, if the Work Item Type is Task, I want to see what the maximum task would be.

               

              I am confused as to how to tackle this problem, any help would be great!

              • 4. Re: Issue with IF and THEN statement
                Rohin Mahesh

                Thank you for getting back. This did not end up working in my case. Here is what I am trying to achieve:

                 

                I essentially have these columns: ID, Work Item Type, Remaining Work and Effort.

                 

                I want to get the maximum Remaining Work for when the Work Item is Task. I want to be able to use the ID to find out if the Work Item Type is Task, what is the Maximum Remaining Work.

                 

                I then want a visualization with Remaining Work and Effort as the X and Y axis variables. I then wanted to data to be filled with ID. When I hover over the ID, if the Work Item Type is Task, I want to see what the maximum task would be.

                • 5. Re: Issue with IF and THEN statement
                  Shinichiro Murakami

                  HI Rohin,

                   

                  Please attach sample data as twbx format.

                  And illustrate your issue and expected result.

                  Without data, it's is very difficult and very inefficient to assist you.

                   

                  Regards,

                  Shin

                  • 6. Re: Issue with IF and THEN statement
                    Rohin Mahesh

                    Hello,

                     

                    Thank you for the response. Unfortunately, it is against my companies policy to share any information about this dataset. That would have been the first thing I did when creating this post but that isn’t an option on my end. With that being the case, any help that can be done would be greatly appreciated!

                     

                     

                     

                    -Rohin

                    • 7. Re: Issue with IF and THEN statement
                      Bryce Larsen

                      Hi Rohin Mahesh

                      Does something like the following work?

                       

                      My suggestion, however, would be to calculate Max work per Work Item Type, and then have a separate sheet of IDs. The reason being is you might have two IDs (or tasks) that are both equivalent to the max remaining work. In this case you wouldn't be able to show them in the tooltip. With this new sheet you can add an action filter to drilldown from the above bar chart.

                       

                      Alternatively you could always show the IDs by default and use same function or exclude LOD. Then you see the bars and the IDs (maybe even multiple) per Work Item Type:

                       

                      Hope this at least helps get you started.

                      • 8. Re: Issue with IF and THEN statement
                        Rohin Mahesh

                        Hello,

                         

                        This is exactly what I am trying to do but with different variables on the graph! The requirements however wants everything on one graph, which was what caused my confusion. How would I calculate the Max work per Work Item?

                         

                        Do I then drop that calculation into detail so that when I view the bar chart for an ID by seeing if it is ‘Task’ Work Item and what the Max Remaining Work would be?

                         

                        Hope this makes sense! Trying to figure out how to move in the right direction.

                         

                         

                         

                        -Rohin

                        • 9. Re: Issue with IF and THEN statement
                          Bryce Larsen

                          Could you try drafting you desired end viz in PowerPoint, Paint, or anything else? Would be much easier then.

                          • 10. Re: Issue with IF and THEN statement
                            Rohin Mahesh

                            Hello,

                             

                            The ideal graph would have Remaining Hours (or average) on the Y axis, and Effort (or average) on the X axis. Each data point should be the ID variable. When I want to over over one of the these datapoints I want to see what the Work Item Type is, and if the work item is “Task”, then I want to see the maximum hours that Task took.

                             

                            If the work item is not Task, then I would like to see what the average Remaining Hours and Effort would be.

                             

                            Hope this makes sense, I have attached the paint picture to this message.

                             

                             

                             

                            -Rohin