11 Replies Latest reply on Nov 29, 2018 5:41 PM by tanvi khanna

    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:



                  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



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

                        Go to Analysis Menu

                        You had Aggregate measures Checked



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

                        Now See



                        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



                          • 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.

                            • 11. Re: Count of number of records
                              tanvi khanna



                              I have a question regarding filtering based on count of total records after applying filters. Since I cant share the actual data and dashboard I have created a dummy data and dashboard for reference.

                              In attached workbook: filters: age, country

                              total records: 22

                              view: % of sum of weight of records by frequency and wave (for reference I have labelled the %sum of weight + count of weight on the bar chart)

                              What is needed - after filtering on age and country, the count of records reaches a threshold (say less than 5)  - for this sample size the view should not be available but should be seen when total count of records in view > 5

                              What I have tried - as you might see in the workbook I created a filter applicable to the other worksheet, count of records >5. This is filtering out every bar in the view whose count of record <5, however I need it to filter only when the total records < 5. To illustrate, in the below image the count of records (last column) for each row is less than 5 except the third last row, hence after applying the filter I made it gives only that particular row. Whereas the actual result should give me everything since total records are 22 which is greater than 5. please find workbook attached.


                              I want to limit views by sample size of view (total records in the view) not count of records in each row.