1 2 Previous Next 21 Replies Latest reply on Nov 8, 2018 10:31 PM by Ombir Rathee

    "Group By" in Tableau

    Ryan Schultz

      I am attempting to group data similar to how you would with a "Group By" function in SQL.  I am attempting to use the group feature in tableau but am not getting the desired result.  I have 4 fields on sheet 1 in the attached file: Time, Tank_1_Recipe_Stage, Batch, Tank_1_NP_7_Total.  What I want to see is the last record for each Recipe_Stage in each batch.  In addition, If the Tank_1_NP_7_Total value is the same as the previous Recipe_Stage, I would like it io return a value of 0.  I am attaching the tableau file as well as an excel workbook with the desired result.   Any help is very much appreciated. 

        • 1. Re: "Group By" in Tableau
          Deepak Rai

          Hi Ryan,

          Not sure about your 2nd Requirement but first is here:

          You need to use a last Statement and make it restart each Batch

          Thanks

          Deepak

          If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

          • 2. Re: "Group By" in Tableau
            Michael Gillespie

            This might help, Ryan.  it's a question of thinking about it in Tableau's vocabulary rather than in SQL.

             

            Tableau - Similar function to SQL Group by - Stack Overflow

            • 3. Re: "Group By" in Tableau
              Ryan Schultz

              Deepak,

              Thank you for having a look.  The data that you used for your calculation is actually my desired output.  I attached 2 files, the other file contains the dataset.  

              • 4. Re: "Group By" in Tableau
                Joe Oppelt

                This would be easy with LODs.  But you have that Batch table calc in there, and you've cut off your access to LODs because of it.

                 

                And I notice that you have the time field first.  Is the positional order of the pills on ROWS crucial?

                • 5. Re: "Group By" in Tableau
                  Ryan Schultz

                  I have seen this.  Thank you.  Part of my problem (and I could be wrong) seems to be that I am trying to group by a calculated field rather than a dimension.  I don't know if this is possible, or what the workaround is. 

                  • 6. Re: "Group By" in Tableau
                    Ryan Schultz

                    The position isn't crucial to me.  However, it seems that the position matters to the calculated field "Batch" . That I have created.  I'm still pretty new to tableau so I'm really struggling through this project right now.  What are LOD's?

                    • 7. Re: "Group By" in Tableau
                      Joe Oppelt

                      What is [Batch] doing?  (In a sentence.  Not in calc code.)  Maybe we can replace it with LOD.  At a minimum though, if I understood what it was doing, I could help you with table calc settings so that position on ROWS won't matter.

                      • 8. Re: "Group By" in Tableau
                        Ryan Schultz

                        This is raw data coming out of an irrigation system. Each batch consists of some combination of Recipe_Stages.  The data doesn't have the concept of a batch in it.  What I am trying to accomplish is grouping the data  so that each time the tank begins filling (A change from Recipe_Stage 0 to Recipe_Stage 1) a new batch is created.  I want to know how much of each ingredient was injected into the tank during each batch, which would be the last value of each Recipe_Stage for each Batch . Hopefully that makes sense.  Please let me know what else I can provide.  Thank you for taking the time to help. 

                        • 9. Re: "Group By" in Tableau
                          Joe Oppelt

                          And what constitutes the last value in a recipe?  The highest date-time stamp?  (Ditto the first value -- the lowest?)

                           

                          Can there be multiple values for batch in a given recipe step?  If so, what delineates a change in batch within a recipe step?

                          • 10. Re: "Group By" in Tableau
                            Ryan Schultz

                            The last value in any Recipe_Stage would be the last time stamp before a change in Recipe_Stage.    The Recipe_Stages are repeated over and over though out the day which is why I'm trying to group them into batches.  So, Batch 1 could have Recipe_Stages 0,1,2,3,4,8,16... Batch 2 could have Recipe_Stages 0,1,2,4,5,8,16... and so on.  Recipe_Stage 0 is when the system is sitting idle, which it returns to between every batch.  Recipe_Stage 1 is the tank filling. Recipe_Stages 2-7 are when various ingredients are being injected into the tank, Recipe_Stage 8 is the Dispense Stage.  So I am saying that every time the Recipe_Stage changes from 0 (Idle) to 1 (Tank Fill) that constitutes the beginning of a new batch. 

                            • 11. Re: "Group By" in Tableau
                              Ryan Schultz

                              The reason  I am looking for the last value in each Recipe_Stage is because that value will reflect the final state of the batch before moving onto the next stage. 

                              • 12. Re: "Group By" in Tableau
                                Michael Gillespie

                                this is sounding more and more like a problem that needs to be solved first at the data level before we apply Tableau to it. There is a lot of grouping and sorting and categorizing going on.

                                 

                                Have you tried to use Prep to make some sense of the data first?

                                 

                                Michael Gillespie

                                651-329-9638

                                • 13. Re: "Group By" in Tableau
                                  Joe Oppelt

                                  So you're saying that as time goes along, the recipe changes.  And all the time stamps within a recipe step constitute that step.  And in a given day, we could hit that same recipe step several times.


                                  If you had just one more field -- such as [Iteration] -- this would be easy.

                                   

                                  Is there any chance of adding something to the data for that?

                                  • 14. Re: "Group By" in Tableau
                                    Ryan Schultz

                                    I have actually already solved this problem in Alteryx.  I attempted to do this in prep but it seems a little more limited in its capability than Alteryx.  I was hoping Tableau could handle it, so that I could have a dashboard that updated in real time.  However,  as I am researching this problem, I have been coming to the same conclusion.  If I could solve this in Prep, is it possible  to schedule those workflows to update the tableau workbook regularly, without the need for some additional license? 

                                    1 2 Previous Next