3 Replies Latest reply on Jun 8, 2012 9:44 AM by Jonathan Drummey

    Help with number of records

    Jeremy Christman

      I have a dataset in which I am using 1 variable as a dimension and the other as a measure. I am plotting the average of the measure vs the dimension. I would like to display the number of records as a label. But I have some missing data for the measure. So when i use the sum number of records for the label, it is counting the number of records for the dimension. How can i change it to be the number it used for the average? I have attached the file as well as some simple data for this. The number of records should be 2 for when the rating is 2,0 and 5 and 3 otherwise. Thanks for your help.

        • 1. Re: Help with number of records
          Alex Kerin

          You could either create a calculated field:


          if not isnull([rating2]) then 1 else 0 end


          or use count([rating2]) instead of sum([Number of Records]) - count ignores the nulls

          1 of 1 people found this helpful
          • 2. Re: Help with number of records
            Jeremy Christman

            I realized that I oversimplified the problem when I posted it on here. I am actually using measure values and measure names. And the missings are not consistent across the variables. So, here is a simple example that I hope better shows my issue.

            • 3. Re: Help with number of records
              Jonathan Drummey

              Hi Jeremy,


              I'm not quite understanding your goal. There are 18 rows in the file, 3 per value of rating. In the Sheet 1 you posted, the view has the rating dimension on Columns, so SUM(Number of Records) as the Label is accurate - 3 rows were used to calculate each value.


              If you use the calculation Alex described, that will get rid of Null values, so rating =2 would return 2, etc. Nothing would return more than 3, so I'm confused about how you got a number of records = 5. Are you trying to count all the non-null values of rating2 and rating3 for each rating?


              Also, when you are posting a workbook, please post a packaged workbook (.twbx), that way your data will be included in the workbook and it's easier to open.