1 2 Previous Next 28 Replies Latest reply on Jun 24, 2015 12:21 AM by Bora Beran

    How to sum distinct Status

    phuviet.pham

      test.png

      Hi everyone,

       

      i have the Worksheet as the attachment. i want to count the number of each Status base on 2 Column: max Status and Min Status. i have calc like this:

       

      if [count selection]="count max status" then SUM ([max Status])

      elseif [count selection]="count min status" then SUM ([Min status])

      end

       

      however the result is not correct because there are many duplicate rows. the correct result is that:

       

      when i select "Count max Status" then green:2 and red:1

      when i select "Count min Status" then green:1 and red:2

       

       

      Can you please help me how to do that?. Thanks

        • 1. Re: How to sum distinct Status
          phuviet.pham

          Hi is there anybody can help me?. can we Sum distinct the column: Max Status?

          • 2. Re: How to sum distinct Status
            Rody Zakovich

            Hello Phuviet,

             

            Since you are on 8.X, this was a little more difficult then I intended. In addition, the underlying data shows a lot of duplicate records, which made added to the complexity.

             

            Either way, here is the attached workbook.

             

            I will start putting together screen shots and explanations on how it did this, because it involved a lot of table partitioning.

             

            Regards,

            Rody

            1 of 1 people found this helpful
            • 3. Re: How to sum distinct Status
              Rody Zakovich

              Ok first, we need to address the fact that you have you a lot of duplicate records, and you have you have a lot of fields in the view, which means all of those duplicate records are being brought into the partitions.

               

              This means if we do SUM, or even WINDOW_SUM, it is going address all of those duplicate rows. We could normally handle this by tweaking what the Table Calc is Addressing and Computing on, but we have a lot of fields in the View, so this method won't work.

               

              So to get around this we need to address the Status, and the First instance within the partition

               

              Count of Red.png

              This will give us only 1 instance for a particular status.

               

              You have to duplicate this for each status.

               

              Next we need to create Table Calc to build on top of it, and get the Total count for the Status

               

              Actual count.png

               

              Once again, you need to duplicate this for each status

               

              Then drag both of those Measures onto your Text Mark, and clean it up

               

              6-17-2015 12-17-06 PM.png

               

              Since we are only given a value for the status type, we can put these next to each other.

               

              Finally we want to get rid of the excess columns. we can do this by creating a Calculated field

               

              FIRST() = 0

               

              Place that onto the Filter shelf and compute using status

               

              6-17-2015 12-19-25 PM.png

               

              I hope this is helpful.

               

              Regards,

              Rody

              1 of 1 people found this helpful
              • 4. Re: How to sum distinct Status
                Rody Zakovich

                Simon Runc

                 

                Hey Simon, sorry for calling you in so much, but you are always extremely helpful!

                 

                The method I used works, but it is a complicated processes (Might have gone a little too far outside of that box).

                 

                The three big issues I had here was 1. There are duplicate records, and 2. There is a lot of fields in the View, and 3. It's not V9

                 

                Can you let me know if there is any easier way? His original workbook is attached in the first post.

                 

                Thanks,

                Rody

                1 of 1 people found this helpful
                • 5. Re: How to sum distinct Status
                  Simon Runc

                  hi Rody,

                   

                  Not a problem...always happy to help.

                   

                  I see what you mean, it does seem 'unexpectedly' complicated, for what, 'on the face of it' doesn't look like it would be so tricky.

                   

                  I've had a quick play, and think that you do need to handle the 2 status' in different calculations, although I only tried a few other ways of concatenating some sort of unique string, and doing a window_sum(countd...) type thing, but couldn't get it to work.

                   

                  However the solution you'e given here works, which is 99% of the battle! and I think the real answer might be to see if the data can be de-duplicated at source. I found, from bitter experience, that a bit of extra time spent on data-prep can save hours in the long run.

                   

                  I'm away for a few days, but I'll take a further look when I get back (I love these kind of logical problems...they usually get the better of me, but I still enjoy them!!)...Like you I feel there is a simpler solution out there!

                  1 of 1 people found this helpful
                  • 6. Re: How to sum distinct Status
                    Simon Runc

                    hi Rody,

                     

                    ...back from my hols! and I've had a further play with this and think I've found another (slightly simpler solution)

                     

                    In this solution I've created calculated fields for all the elements for explanatory reasons, but could be combined into 1 or 2 in a final solution.

                     

                    So the first thing I created was a measure selector, called 'Selected Status - Min or Max' with the formula

                    if [count selection]="count max status"  Then [max Status]

                    elseif [count selection]="count min status" then [Min status]

                    end

                     

                    Then I created a field which creates a concatenation string of the class & project, where the selected status = 1, called 'Concat Selected Status'

                     

                    IF [Selected Status - Min or Max] = 1 THEN [Class]+[Project] END

                     

                    NB. Great Tip btw, as you'll see there is no ELSE statement here so the false of this logic statement is NULL, and NULLs don't get counted or COUNTD'ed (if that's a word!)

                     

                    Now at this point, just putting status *Red/Green' on the row pane, and doing a COUNTD on this field gives the correct answer, but to get the correct answer with the extra dimensions in we need to put this into a Window_Sum Table Calc.

                     

                    This field is CountD Window Sum, with the formula

                    WINDOW_SUM(COUNTD([Concat Selected Status]))

                     

                    I then set the compute using as follows

                    Table Calc Set Up.JPG

                     

                    One thing you'll also notice is I set the Min and Max status in the view to Attributes so I could ignore them in Table Calcs.

                     

                    I think this does what we want, but you may want to double check.

                     

                    What a deceptively tricky problem!! (although I must admit to quite enjoying it!! )

                    1 of 1 people found this helpful
                    • 7. Re: How to sum distinct Status
                      Matt Lutton

                      Version 9 (LOD expressions) would make this very simple, I believe!  Upgrade ASAP! 

                      1 of 1 people found this helpful
                      • 8. Re: How to sum distinct Status
                        Rody Zakovich

                        Simon this is great! Much easier (To understand and implement) than my original solution.

                         

                        Using the concatenation of [Class] and [Project] was a really smart approach

                         

                        The one thing I really liked, was using ATTR() for the min and max status. That really makes things easier, and more flexible, as we don't have to add a new calc for every possible status. Great work!

                         

                        If you come across any of these "Tricky Problems", please ping me on it. I, like yourself, love working through theses puzzles. And am very, very reluctant to ever say, "This is not possible"!

                         

                        Thanks again and best regards,

                        Rody

                        1 of 1 people found this helpful
                        • 9. Re: How to sum distinct Status
                          Rody Zakovich

                          Matt Lutton

                           

                          Hey Matt,

                           

                          I think we would still run into a similar problem with V9, as there are duplicate records. But I am very interested to see what your approach would look like.

                           

                          Of the top of my head, I think it would be possible by Excluding a lower level dimension, but not quite sure how that would look.

                           

                          Your thoughts?

                           

                          Regards,

                          Rody

                          1 of 1 people found this helpful
                          • 10. Re: How to sum distinct Status
                            phuviet.pham

                            Hi everyone,

                             

                            i used Level of Detail Calculation and it work very good for me.

                             

                            However i got another Problem. my Real Data has more than 46.000 records. before i use Level of Detail Calculation Tableau shows me Report very quickly. But when i create the Calculation to sum distinct Value and upload the Report on Server, Tableau works very slowly. it takes me alway 4-5 Minutes. Sometime Tableau cant Show me the Report and send a Error Meassage.

                            I wonder if the Level of Detail make Tableau slower?

                            • 11. Re: How to sum distinct Status
                              Matt Lutton

                              Phuviet -- can you upload you V9 workbook for Rudy to see?

                               

                              Rudy, I have not studied this problem in depth at this time -- but I was imagining that LOD expressions would allow you to do this more readily since we can calculate at different levels of granularity, regardless of dimensions in the view (like taking the SUM of MAX values, or similar -- without resorting to Table Calculations).

                               

                              I would need to look at the example a bit more closely and understand the expected results to show you how I'd approach it.  If time allows me to dig into this later, I will try to do so -- I have quite a bit going on right now!

                              • 12. Re: How to sum distinct Status
                                phuviet.pham

                                Thanks Pooja Gandhi for help me with this Calculation.

                                • 13. Re: How to sum distinct Status
                                  Simon Runc

                                  Hi Phuviet,

                                   

                                  Are you connecting directly to your data source or are you using an extract (.tde)? If you are using an extract you could try to 'optimize' the extract and then republish. Like Matt I've not studies LoD performance, but this might help.

                                  • 14. Re: How to sum distinct Status
                                    phuviet.pham

                                    Hi Simon,

                                     

                                    Ist live Connection. When i upload the Report on Server i muss make sure that the live Connection to Database is choosen.

                                    1 2 Previous Next