4 Replies Latest reply on Dec 4, 2018 4:30 AM by Sander Binda

    Size function based on?

    Sander Binda

      Hello all,

       

      I've got a question about the size function. I have difficulty to find any useful information about this function.

      I currently got a crosstab (see attached image) with 3 dimensions: 'customer', 'article' and 'loading week'.

      Then I got different measures like amount of sales, turnover etc. (left them out because it's privacy sensitive information).

       

      One of the measures is the average turnover per sale. I faced the difficulty that my grand total was based on all underlying rows.

      So one of the friendly people on this forum proposed the following calculated field:

      IF SIZE() = 1 THEN

          SUM([Turnover])/SUM([Sales])

      ELSE

          [Calculation 1]

      END

       

      Calculation1 is an LOD function calculation the average turnover for every row.

      This works all fine, thank you for that but I try to find the logic behind it.

      All information I can find is that Size returns the number of records and in my case Grand total is one record thus only the grand total is calculated using SUM([Turnover])/SUM([Sales]).

      But the row under the Grand Total is also just one record. That particular customer only bought once that article in the selected weeks.

      Where is the Size function based on? How come the Size function applies to Grand total and not on the row below, both are 1 records.

      What does record mean in this case? Does it count all records for customer + article + loading week? or just based on the amount of times 'customer' is in the underlying data?

        • 1. Re: Size function based on?
          Amit Narkar

          The logic which you have mentioned above is likely suggested to show different numbers for totals and otherwise.

          Take a look at simple table below

           

          Totals in Tableau are calculated using different queries. As per above snapshot you will see that total shows size=1.

          size is table calculation (means it is based on a data what you see in a view only)

          Table Calculation Functions - Tableau

          • 2. Re: Size function based on?
            Sander Binda

            Hello,

             

            Thank you for your quick response.

            So, for my understanding. The Size function calculates the amount of rows in the view for each first dimension of the crosstab.

            Your first dimension is category, my first dimension is customer. Is this assumption correct?

             

            Second question, what if for example Furniture has only 1 subcategory? As you can see my my first customer has only 1 entry is his size also 1 then?

            (Or maybe better how do I add such a column like you did to calculate whatever size is calculating?)

            • 3. Re: Size function based on?
              Amit Narkar

              It is not necessarily first dimension in a view. Results may vary based on how to set table calculations

              Like in below case using Table Down, I am saying return overall size for sub category, manufacturer combination. i.e 22

              Total=4 i.e number of subcategories in view

              Grand total=1

               

               

              In below scenario, I am saying give me size for each subcategory based on manufacturers available in a view i.e you see copiers(4),Labels(5) etc

              Total=1 as we need to get results  for each pane.

              Grand Total=1

               

              In below scenario, computation is set to provide results for sub-category and manufacturer based on size calculated for sub-categories.

               

              You can have a look at below video for better understanding.

              https://www.youtube.com/watch?v=XPYtenDXCdI&t=1275s

              • 4. Re: Size function based on?
                Sander Binda

                Thank you, this solved my answer