3 Replies Latest reply on Jan 22, 2013 1:19 PM by Shawn Wallwork

    Number of records duplicating in each column

    Adnan Akhtar
    Adnan Akhtar Jan 18, 2013 7:44 PM

      Hi,

      I created a sheet with number of user who are delinquent on their payments. The columns are divided by range of days they are delinquent.

      The problem is that the same number of records are being duplicated in each column rather than just being displayed in their respective field.

      For example if there are 3 users delinquent 1-30 days, the number is also being captured in 30-60 days. I attached the data for reference.

      Your assistance is appreciated.

      Regards,

      Adnan Akhtar

      • 128 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Number of records duplicating in each column
          Shawn Wallwork
          Shawn Wallwork Jan 19, 2013 5:52 AM (in response to Adnan Akhtar)

          Adnan, I can't say I completely understand your data and how your viz is working, but when I changed your COUNT() to COUNTD() I was able to get the various bins to populate differently. Is this what you're looking for? If so, you'll need to extract your data, which makes the COUNTD() function available. By doing an extract you are switching from the Excel Jet engine (doesn't' support COUNTD) to the Tableau engine that does.

           

          --Shawn

          • 2. Re: Number of records duplicating in each column
            Adnan Akhtar
            Adnan Akhtar Jan 21, 2013 2:55 PM (in response to Shawn Wallwork)

            Shawn,

             

            Switching the count to sum have seems to solved the problem. The issue is that the columns named onetothirty_day, thirtyonetosixty_day has dollar amount in them. I am trying to get a count of how many record each column has. Switching to sum shows the actual dollar amount owed from these customers. If a column has $0 then it shows zero. I think the "count" is also counting the zero as one records. If you look at the data I attached first column ACF (customer site) has 3 total records. 2 records are delinquent ( total of $750) onetothirty_day and one record is delinquent sixtyonetoninety_day.If I add the account name to the detail, it shows each customer amount but not the count. I think I can live with what I have for now. If I can show the count and total on another sheet, that would be great.

            Thank you . I appreciate the assistance.

            Regards,

            • 3. Re: Number of records duplicating in each column
              Shawn Wallwork
              Shawn Wallwork Jan 22, 2013 1:19 PM (in response to Adnan Akhtar)

              Adnan, you have some strange records with $ amounts in both 1-30 and 31-60 so your count can total 17 when there are only 16 records. This threw me for a bit. Anyway I've attached a workbook that does what I think you're looking for. Rather than using a count function, I set up an IF/THEN statement:

               

              IF [OneToThirty_Day] > 0 THEN 1 ELSE 0 END

               

              This gives you a count without having to put the individual records in play.