10 Replies Latest reply on Jun 24, 2018 5:50 AM by Jerry Flatto

    Count of number of records

    Jerry Flatto

      Below is a fairly simple chart.  I am looking at the number of Payment Types (e.g., cash, credit) for a sales transaction.  Thus, I have one transaction that has 10 payment types associated, two transactions with 9 payment types, etc. 

       

      What I want to do is get a count of number of transactions versus the number of payment types.  Thus, I would have a chart that has xxx transactions have 1 payment type, xxx have 2 payment types and so forth up to "1 transaction has 10 payment types".  

       

      This should be relatively easy to do but I am not sure how.  Any help is appreciated.

       

       

      # of records.jpg

        • 1. Re: Count of number of records
          Deepak Rai

          {FIXED PK:COUNTD(Payment Type)} Bring This to Columns as Dimension

           

          and COUNTD(PK) Drag It To Rows

          • 2. Re: Count of number of records
            Jerry Flatto

            Thanks but I am not getting the correct answer but that is my fault.  They are three payment types - check, credit and cash. See the table below.  Your answer did identify distinct payment types.

             

             

             

            In hindsight, I should have made clear that a single transaction can have the same payment type (e.g., cash) more than once.  In other words, "credit" may appear 5 times for a single transaction if the customer used multiple credit cards.   If you look at a sample of my original data below, you will see that the single ticket sale had 8 cash payment types and 1 credit payment type.  {And no, no idea why this would occur in real life but this is the data I am being given for analysis so I am not in a position to look at "why" this occurs. }

             

            Thus, I want to get a table telling me how many sales had more than one payment associated, even if the payment type was the same.

             

            To be honest, where this becomes an issue is that I perform a "join" between the payment type and the sales.  Thus, my join will give incorrect results as I expected a single payment type for each sale.  Thus, I am trying to get a handle on how many transactions have multiple payments even if they are the same type (credit or cash). 

            • 3. Re: Count of number of records
              Deepak Rai

              This happens when we dont have Packaged workbook.Please attach one to help you. may be with fake data

              • 4. Re: Count of number of records
                Jerry Flatto

                Sorry.  I just created a sample twbx file and attached it along with the csv file.  I have three payment types: cash, credit, check.  However, a single transaction can have multiple payments of the same type.    Thus, I want to generate a table that shows how many transactions have x number of payments associated with it, even if the payment type is the same.  Thus for the chart below, I would expect a table that shows that 1 transaction has 9 payments, 1 transaction has 3 payments, 3 transactions have 2 payments, 3 transactions have 1 payment.

                 

                • 5. Re: Count of number of records
                  Deepak Rai

                  Here It is:

                  Thanks

                  Deepak

                  If it Helps, pl mark it Helpful and CORRECT to Close Thread

                  • 6. Re: Count of number of records
                    Jerry Flatto

                    I appreciate your help.  I am now going crazy. I can not match your twbx file.   See my attached sheet that I created from scratch.  The first issue is the "index" page.  You show 21 rows while I only show 14.  21 is the correct answer. 

                     

                    It is probably something obvious but just not sure what. 

                    • 7. Re: Count of number of records
                      Deepak Rai

                      I would check what’s going on in few mins

                      Thanks

                      Deepak

                      • 8. Re: Count of number of records
                        Deepak Rai

                        Go to Analysis Menu

                        You had Aggregate measures Checked

                         

                        Now,

                        When I uncheck it see Rows become 21, because your data was getting Aggregated Earlier, I removed aggregation

                        Now See

                        Thanks

                        Deepak

                        If it Helps, Please Close Thread by marking my Reply as Helpful and CORRECT.

                        1 of 1 people found this helpful
                        • 9. Re: Count of number of records
                          Deepak Rai

                          Your new Data has TENDER ,,,I had Tender

                          So u are not matching...Use this for your data

                          Thanks

                          Deepak

                          • 10. Re: Count of number of records
                            Jerry Flatto

                            Thank you so much for your help.

                             

                            If I could ask one related question which is where this whole issue started.  I have attached the related files.  In reality Sales and Tender are two separate files.  I need to join the two files.  When I do that, the join results in additional rows of data than what I should have.   For example, looking at the Sales csv file, I have a total of 18 sales so should have 18 rows in my Tableau sheet.  However, because some Sales seem to have multiple Tenders associated, the join process creates extra rows so that in the attached Tableau file has 38 records, thus 20 additional rows.  Is there a way to take only the first Tender row associated with a sale and ignore the rest of the tender types.  In reality, it is extremely unlikely that a transaction has more than one payment associated and thus it is assumed that the extra Tender rows for the same transaction are incorrect and should be deleted.

                             

                            If it makes a difference, I am using Tableau Prep to clean the data so I can modify the Tender file inside of Tableau Prep before I perform the join.