3 Replies Latest reply on Nov 9, 2017 6:10 PM by Toby Bernhagen

    Count of records

    Toby Bernhagen

      Morning

       

      I have a string field called 'Receipts' which contains either Yes, No or Null.

       

      I am trying to count the records that only have 'Yes'

       

      I have been through the forums and applied various methods i have found, the latest being below however it doesn't want to work, as every way i try returns a count of all records, what am I missing?

       

      COUNT ( INT ( [Receipt] = "Yes" ) )

        • 1. Re: Count of records
          Brent Mathison

          Hi Toby. In your formula, it looks like you are attempting to convert a string to an integer, and that will not work for those values. You could use something like this as a formula:

           

          SUM(IF [Receipt]==“Yes” THEN 1 ELSE 0 END)

           

          or if you only need the “Yes” values in your viz, you could add [Receipt] to the Filters pane and filter on the “Yes” value.

          • 2. Re: Count of records
            Toby Bernhagen

            Ow ok i had to change the " to ' but it worked a treat thanks.

             

            I have only ever done and this is recently some stuff in access so trying to learn the nuances of calculation, I will have to research more for a guide of sorts.

             

            So if I was trying to do something like below is this correct thinking?

             

            SUM(IF STARTSWITH([AppealID],"CDM") AND [Amount] >= 20 Then 1 else 0 End            

             

            So count of records when appeal starts with CDM and amount is equal or bigger then 20

             

            Where AppealID is a string

            And AMount is a number

             

            Thanks for the help Brent.

            • 3. Re: Count of records
              Toby Bernhagen

              Actually had time to check and it does