11 Replies Latest reply on Feb 19, 2015 12:35 AM by Can Akin

    Aggregating Aggregates?

    Can Akin

      Hi Guys,


      I am somehow stuck with one visual. I did some calculations and filtered out some stuff. Now I want to show the sum of a result column (consisting of positive and negative values). My problem is that I cannot add them all together. Now the Bar chart shows a stacked bar with the positive an negative values. Does anybody know, how to add them to one number?



      The left column in this picture should add to approx. 100 (-200 +  300)


      Thanks a lot in advance! I am glad for any hint.

        • 1. Re: Aggregating Aggregates?
          Mark Lake

          Can you post your TWBX?

          • 2. Re: Aggregating Aggregates?
            Can Akin

            Hi Mark, thanks for the reply.


            Unfortunately the data is very confidential. Otherwise I would have done that.


            I am gonna check if I can recreate the example with dummy data..

            • 3. Re: Aggregating Aggregates?
              Joe Oppelt

              You want to use the WINDOW_SUM() function to add up what is in the window.  This becomes a table calc.


              Chances are, if you run the table calc using TABLE(down) it will do what you want.  But you may have to use the ADVANCED feature to tell it to restart for each stacked bar.


              And it's hard to coach how/why the various tacle calc options work for your example without actual data to work with.


              When I see someone say they can't share an example because of proprietary issues, I point them to a list of suggestions I made to another forum participant.  See here:

              Re: Linking dates from calendat table to other tables

              • 4. Re: Aggregating Aggregates?
                Can Akin

                Nice trick, thanks.


                Here is my twbx!


                I left the problem a little earlier in this one because I think it is easier to catch. I basically need the process times summed up for the process steps, which are not excluded by (Z_Excluder = 0). This sum I would like to visualize in a bar chart. In this case the sum would be 566.

                • 5. Re: Aggregating Aggregates?
                  Joe Oppelt

                  Sede attached.


                  I created a second calc for Z_ProcessTime that only displays a value if it should not be excluded.  I added it to ROWS, but hid the column so it doesn't display.  (But you can unhide it to see what it's doing.)


                  Then I created a table calc to add up all the [Z_ProcessTime (copy)] values.  I had to use ADVANCED on that calc to include all the components that are on the shelves, and then tell it to restart for every [NL].  That makes it sum up for the entire [NL] instead of restarting whenever [Exercise Number] or [Process Step] changes.


                  I'm not sure where/how you want it to look as a bar chart.  It's a constant number for all the rows.  I put it on the LABELS shelf so that it displays as a value, not a bar.  But my purpose is to show you how to make that calc work for you, and you can do whatever artwork you need to display it.  (I also put it in the title and have that showing.)


                  You'll see that if you de-select Process Steps that have non-excluded numbers (such as #3.4) the window sum changes accordingly.

                  1 of 1 people found this helpful
                  • 6. Re: Aggregating Aggregates?
                    Can Akin

                    Thanks! That takes me a big step ahead!


                    Somehow I have got problems to display the 566 in a simple bar chart. If I take out all the other measures from "row" and only leave Calculation2, the 566 is stacked seven times.. I think it has got something to do with the measures in the details pane, but I cannot take them out, because the calculation is based on it, right?


                    In the End it should look like this:


                    City 1 ||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 566

                    • 7. Re: Aggregating Aggregates?
                      Joe Oppelt

                      Yes, and I had to include them in the definition of the table calc.  I couldn't get the calc to work without them.  So tableau is partitioning with them.


                      I thought about working on unpacking all the layers of table calcs you have in there.  I just didn't have the bandwidth to reverse-engineer them all to figure out what they're all doing.  It's possible that you might be able to simplify it all.

                      • 8. Re: Aggregating Aggregates?
                        Joe Oppelt

                        I just had an idea.


                        I added one more calc to the filter.  Just get the first of those partitions.


                        See [Get first row] in the attached.


                        I had to add [Data Normiert] to the ADVANCED setup.  I tried [STATUS] and that didn't do it, nor did adding both [Data Normiert] and [Status].  I don't know enough about yoru data and what all is happening in all those calcs, but a little trial and error got me the attached result.  I think (I THINK!) if you have more NL values, this will give you a different bar for each NL, but I can't be sure.  The various calcs I create all restart on NL, so that should take care of it, but you'll have to check it out to be sure.

                        • 9. Re: Aggregating Aggregates?
                          Can Akin

                          Awesome, thanks a lot for your help!

                          • 10. Re: Aggregating Aggregates?
                            Joe Oppelt

                            For the record, in the [Get first row] calc there is a commented line.  It's just another way to do the same thing.  I meant to mention that.

                            • 11. Re: Aggregating Aggregates?
                              Can Akin

                              Perfect, thanks.


                              I used your methods to solve a dozen other problems I had before ...