10 Replies Latest reply on Aug 9, 2016 5:55 AM by savas.ziplies

    Running distinct count over grouped IDs


      Hi everyone


      I am fighting with this topic for some time now and am really getting more grey by the hour.


      I am trying to have a waterfall chart that displays the count of IDs, running without double-counting an ID (see the example workbook).


      So, in the example workbook, I have groups in one datasource, matched through a key to the main source. The main source has an amount of IDs per group, matched by key. Now, I would like to count how many distinct IDs every group brings to the table. The total amount is correct with 8, but the goal is to have it running, so have

      3rd: 5

      Evo: 2 (or 7)


      Total: 8


      So that the running waterfall results in 8.


      A big problem I have is that I am not allowed to change the primary data source, only the secondary at max. I have googled and found many ideas and posts around this topic, but I wasn't able to somehow translate anything to my example.


      I hope anybody can help me here. You will be guaranteed a sponsored beer if we ever meet




      Many Many Thanks to everyone

        • 1. Re: Running distinct count over grouped IDs

          Hi everyone


          Anybody got any idea how to derive the described scenario?


          I am hammering on this and it feels I am so close but just cannot nail it. I thought taking 'ID' as filter and filter by field count = 1, but this seems not to work (of course it does not, but sounded so close).

          I stumbled over some ideas with an INDEX() but also cannot make it work.


          As you can see in the attached v2 I was able to filter the IDs via the index() somehow to be unique, and I can count up to 8. But the counts are unique per ID, not grouped per Group. That would be cool. And from there to the target waterfall would be the optimum.


          It seems to be so close, but maybe I am just in a delirium.



          Thanks a lot

          • 2. Re: Running distinct count over grouped IDs

            Hi there


            Me myself again ^^


            I am still trying to figure this out. So far I got the correct groups, I got the correct IDs. But any Count I am doing just fails. Am I doing something wrong?


            So, if I neither add the ID to rows/columns or Detail I will not have all groups required and I don't see why.

            But if I add ID I only get the Detail not the Count, as the Count stays per ID then.



            It seems so obvious but I cannot see it. I don't get why I am missing groups if I remove ID from the sheet, and if added I don't get the Count/Sum required. Does anyone has any idea? The workbook is attached.




            Thanks a lot

            • 3. Re: Running distinct count over grouped IDs

              Hi there


              Nobody got an idea? I am really lost.


              I see the reason behind the result, I can see the reason I need the ID to compute the INDEX() partitioning. But it really screws with the overall possibility for me counting the sum per Group.


              I looked at the counts, the INDEX, but everything is segregated by the ID (of course). How could that be combined again?



              Any help whatsoever is appreciated very very much

              • 4. Re: Running distinct count over grouped IDs
                Christina Gremore

                Savas, I think I got it. It's gonna take me a few minutes to do the write up, but wanted to send out a beacon of hope for you!

                • 5. Re: Running distinct count over grouped IDs
                  Christina Gremore

                  OK, here we go:


                  1. Start on Blatt data source

                  2. Drag ID to detail shelf

                  3. Switch to Tabelle data source

                  4. Drag Group to rows. Establish blend on Key. Exclude the Null value.

                  5. Create new calculated field (I called mine "Index count integers") with this formula:


                  WINDOW_SUM(IF [INDEX()]=1 THEN 1 ELSE 0 END)


                  6. Drag your new field to the text shelf. Right click to edit table calc.

                  7. Within the dialog, at the top, switch the calculated field, using the drop-down menu, to INDEX()

                  8. Compute using > Advanced > Partitioning: ID, Addressing: Group. Click OK.

                  9. Within the dialog, at the top, switch the calculated field, using the drop-down menu, to "Index count integers" or whatever you named your new field.

                  10. Compute using > Advanced > Partitioning: <nothing>, Addressing: Group, ID. Group MUST be on top of the list. Click OK.

                  11. Set At the level = Deepest, and Restarting every = Group. Click OK.

                  12. On the Marks card, convert your "Index count integers" pill to continuous.

                  13. Add in your column grand total.


                  (This next part will hide the other values so that you only show 1 digit for each row instead of several.)


                  14. Drag INDEX() field to filter shelf. Select whatever value.

                  15. Right click INDEX pill on the filter shelf to edit table calc. Compute using > ID. Click OK.

                  16. Now set your Index filter to only show the "1" value (can be continuous or discrete)

                  17. Bring me a beer!




                  Workbook attached for example.


                  If this resolves your question, please mark it as the correct answer. Thanks!

                  2 of 2 people found this helpful
                  • 6. Re: Running distinct count over grouped IDs
                    Matt Lutton

                    Yup, one beer for Christina Gremore at the Tableau Conference in Austin this November!  Be there!


                    But seriously, this is awesome help you've received here -- perhaps one day you can pay it back by answering difficult forums questions yourself!


                    Cheers to all.

                    • 7. Re: Running distinct count over grouped IDs

                      Wow, plain Wow. Thanks big time!!! A beer you shall be served if we meet 10 Beers!!! ^^


                      Really really great explanation of how this works with all the steps. Thanks big time!!!


                      I still have to wrap my head around all this computation using above below stuff, but it looks so obvious


                      A tiny tiny follow-up question: I can see the index calculation is per row and restarts, as obviously set before in the computational window. Is there any way to get the previous value? Because of the restart everything is always back to zero and am curious if there is such a thing.




                      Thanks a lot

                      • 8. Re: Running distinct count over grouped IDs
                        Christina Gremore

                        Hm, I'm not sure exactly what end result you're trying to achieve, but you might consider playing with either the LOOKUP or PREVIOUS_VALUE functions in a calculation. Hope that helps!

                        • 9. Re: Running distinct count over grouped IDs

                          Hi Christina


                          I try to achieve a running walk, so increase the following value by the previous value. Unfortunately PREVIOUS_VALUE and LOOKUP do not work, as they just look in there own vicinity, so the previous ID, whereas I would be interested in the previous value of the group.


                          I tried LOOKUP -1, but that just returns nothing. LOOKUP 1 on the other hand gives me the next value. I don't really know why -1 is not working. Probably somehow because of the segregation.


                          Maybe someone has an idea in this case.


                          So, basically what I am trying to achieve is, with the given Workbook from Christina, to have a waterfall walk, so increase the stack up by the previous value + current value. Because of the segregation of IDs it seems LOOKUP and PREVIOUS_VALUE cannot be used. How could you get the previous value of a group to add it to the current is the question? I would like to make it a Gantt Waterfall then.




                          • 10. Re: Running distinct count over grouped IDs

                            Ha, found it ^^ (at least what I was looking for)


                            Now, I have the Distinct Running Waterfall Walk I was looking for originally. Thanks a lot Christina for all the help!!!


                            I hope this helps other people, too. It seems this is a very common topic people are looking at.


                            Regarding the Gantt Waterfall: I was missing to setup the Table Calculations accordingly. So, what I did now is that there is a specific "Running" Measure that sets the calculation of Index Count to the same as described by Christina, INDEX(), too. But for itself, it does not restart at Group. By that, it is increasing.


                            By that, one of my (unfortunately still many) problems has been solved.



                            Again big big thanks!!!

                            1 of 1 people found this helpful