8 Replies Latest reply on Jul 15, 2018 11:57 PM by Adhitya Sanusi

    Counting How Many Entries per Row

    Adhitya Sanusi

      Hi All,

       

      I have difficulties counting how many entries per row. Below is the screenshot for it. The top product providers are Product_Provider_LA. Under it there are some product providers called Product_Provider_NB.

       

      For example DOB  11/09/1957 there are total 3 entries in a row. DOB 06/10/1957 there are 2 entries in a row. In excel, it can be achieved easily by counting how many column that are not blank in a row.

       

      I have tried to use {fixed[DOB_LA] : countd([ProductProvider NB])} but it doesn't give me what I want.

       

      Thanks all.

       

      Kind regards,

       

      Adhi

        • 1. Re: Counting How Many Entries per Row
          Sankarmagesh Rajan

          Hi Adhi,

           

          Please share the workbook.

           

          Thanks

          sakar

          • 2. Re: Counting How Many Entries per Row
            Adhitya Sanusi

            Hi Sankarmagesh,

             

            I have mocked up a dummy data that mimicking the real data. Attached are the spreadsheet and the twbx file.

             

            For example Dob LA 13/02/1966, It suppose to have 3 entries (ProductProvider LA/ Product Provider NB : B/D, C/B, and C/D). However it only calculates 2 entries only.

             

             

            Thanks for your help.

             

            Kind regards,

             

            Adhi

            • 3. Re: Counting How Many Entries per Row
              Zhouyi Zhang

              Hi, Adhitya

               

              Please find my solution below

               

               

              Hope this helps

               

              ZZ

              1 of 1 people found this helpful
              • 4. Re: Counting How Many Entries per Row
                Adhitya Sanusi

                OMG IT WORKS !!!!!!!!!!!!!!!!!!!! Do you know how many people in the office that have been helping me since 2 days ago? 4 people !!!!! and no one know how to do it. You are SUPER LEGEND !!!!!

                 

                So this is the 'CountA' formula in Tableau. Would you mind telling me the understanding behind that formula or how the formula works? It doesn't make sense to me and all those 4 people. LOL... you should see their face when they see your formula. No one understand what it does.

                 

                Thank you very much Zhouyi for your help. You are genius !!!!!!!!

                • 5. Re: Counting How Many Entries per Row
                  Zhouyi Zhang

                  Hi, Adihitya

                   

                  You are welcome and I am glad to help

                   

                  If I understand correctly, you want to count the combination of LA and NB for the day.

                   

                  And if you look at your data in different way as below, you will find the combination A+B has 3 distinct values.

                   

                   

                  While your original calculation only counts LA, so that's why it is 2, but if you just need the number of rows for that day, you can also use count instead of countd. which will give you the same result as my previous reply.

                   

                   

                  Hope this explains well to you.

                   

                  ZZ

                  • 6. Re: Counting How Many Entries per Row
                    Adhitya Sanusi

                    Thanks Zhouyi for the answer.

                     

                    Now it makes sense when segregate the data and you combine LA and NB together (A + B).  Also now I get it as there are D, B and D in the ProductProvider NB but if I do CountD I will still get the result of 2. If I count ProductProvider LA, I also get the result 2 since there are only B and C. Interesting when someone explain it step by step, you can see it properly, but when you see the whole data, you get so confuse.

                     

                    One more thing Zhouyi, if you don't mind, I need to apply some dimension filters. However when you apply the dimension filter (currently Reporting Period), some of the numbers don't change accordingly. How do you make the LOD formula above flexible with the changing of dimension filters (later on i need to add Investigation Type filter)? I tried to put dimension filter on the 'fixed' LOD but it throws off all the calculation.

                     

                     

                    Attached is the twbx that I have already modified according to your advise above.

                     

                    Once again thanks Zhouyi for your help as I am still exploring Tableau (It's kinda fiddly applying Excel knowledge to Tableau).

                     

                    Kind regards,

                     

                    Adhi

                    • 7. Re: Counting How Many Entries per Row
                      Zhouyi Zhang

                      Hi, Adhitya

                       

                      I think probably this is what you need if I understand corrrectly

                       

                       

                      ZZ

                      1 of 1 people found this helpful
                      • 8. Re: Counting How Many Entries per Row
                        Adhitya Sanusi

                        Awesome !!! Yes it works like charm now. It's my first time using context filter. It's another Tableau knowledge that I will use from now on.

                         

                        Thanks Zhouyi, I really appreciate your help. You are Legend !!!!

                         

                        Adhi