6 Replies Latest reply on Oct 30, 2018 5:19 AM by Alex Camatti

    Get number of rows in another worksheet

    Alex Camatti

      Hey everyone, i searched in the foruns of community and googled but i found nothing.

       

      Basicaly my question is, how to get number of records/row of a worksheet in another worksheet?

       

      Look at this scenario: i have "Worksheet 1" that have table calc in the visualization, its show me a list of itens separeted by rows and columns. In another, named "woksheet 2" i only want the number of records of the "worksheet 1" but i dont know how to make this happen. In worksheet 1 i have the table calc, filters and some other informations that are important to show, but i want to make a "indicator" in "worksheet 2" that shows how many itens are the result of the filters that was applied in "worksheet 1". Is that possible to make? If its not, so, how other option i have? The ideia is very simple, but due the limitation of tableau make this a little harder. I'm using table calcs because thats the only way to make a running total (RUNNING_SUM), but with table calc its not possible to make a new worksheet and use it like a filter or in another calculated field without having all of records in a table / visualization, because the table calc make the calculation in visualization, nothing is stored like normal calculated fields. So i thought in this solution and i think thats the only way to reach my goal.

        • 1. Re: Get number of rows in another worksheet
          Joe Oppelt

          By definition, table calcs are evaluated in the context of the table underlying the sheet where they are being used.  (Thus the name, table calcs.)

           

          So if you want that value available in a second sheet, you'll need to calc it in that second sheet too.  Which means you'll need to manage the same structure of rows in the table in that second sheet.

          • 2. Re: Get number of rows in another worksheet
            Alex Camatti

            right... but thats the problem, its a very complicated problem. Is there a way to make running total without table calc? Or, its possible to divide the sheet making table in one side and in another side make a box showing only the number of records?

            • 3. Re: Get number of rows in another worksheet
              Joe Oppelt

              Alex Camatti wrote:

               

              right... but thats the problem, its a very complicated problem. Is there a way to make running total without table calc? ...

              Not that I know if.

               

              ...Or, its possible to divide the sheet making table in one side and in another side make a box showing only the number of records?

               

              Make a copy of your running calc sheet.  re-format it so that you just display the number of records.  (And "number of records" can mean several things, but as long as you know what you need to display, you can generate that number.  Then display that second copy next to sheet #2 on the dashboard.

               

              If you supply a pared-down sample workbook, I can show you what I mean.

              • 4. Re: Get number of rows in another worksheet
                Alex Camatti

                Ok, i tried to abstract the maximum in this example using global super store. The ideia is the same, i have the "ABC Category" and want to show how many costumers are in the filter "Category A" or B, or C, thats depends of filter. that's the point, if i remove all records, table can't do "the job" and all i get is "category C". And all i want is a single number showing how many itens this filter results. The reason for i'm having using table calc is because i need separate the itens in category, and needs to be like the example, by percentage. But this needs to be in "order by <percent of total> 'desc'" and the only way is table calc...

                 

                and... i want to be able to use this number of rows in another sheet... And Yes, this project is very complex.

                • 5. Re: Get number of rows in another worksheet
                  Joe Oppelt

                  I changed the way you'll pick your ABC Category value.  I'm using a parameter, and then using calcs based on the parameter.  See Sheet (2).


                  Then on Sheet 3 I moved things around and I am just displaying the number for that category.  The [Just show one] calc displays the last row of all the rows in the category.  (I left the tooltip values in there so you can see that.  You'll want to yank out those detail values, or even disable tooltips entirely for this sheet.)  This is what I was saying you can place on your dashboard to show the number of rows.

                   

                  And because of the parameter, you can re-calc this value in subsequent sheets if you leave all the rows in the underlying table.  It will require you to make table calc filters on those subsequent sheets, but that's what I was talking about when I said you'll need to re-calc the value on each sheet.

                   

                  the cool thing is that one parameter will control all the sheets this way.

                  1 of 1 people found this helpful
                  • 6. Re: Get number of rows in another worksheet
                    Alex Camatti

                    man, you just save my life, thank you very much. That's what i'm trying to do for 2 weeks, you're the best!