5 Replies Latest reply: Oct 13, 2014 1:37 AM by jagan mohan rao appala RSS

    Count function in straight table

      Hi, I want to count how many of each PO Num exists so I did this simple straight table:

       

      PO NumCount (PO_Num)
      123425

       

      I did another straight table and added in the other variables:

       

      DatePO NumStoreLine CostUnit CostCount (PO_Num)
      30/09/141234A543.654.365
      30/09/141234A543.654.365
      30/09/141234A265.854.365
      30/09/141234A265.854.365
      30/09/141234A1653.254.365

       

      May I know how to get this:

       

      PO NumCount (PO_Num)
      12345

       

      DatePO NumStoreLine CostUnit CostCount (PO_Num)
      30/09/141234A543.654.361
      30/09/141234A543.654.361
      30/09/141234A265.854.361
      30/09/141234A265.854.361
      30/09/141234A1653.254.361

       

      I've already tried formulas such as Count(PO_Num), textcount(PO_Num) and aggr(Count(PO_Num),PO_Num,Store,Date) but I still got 25 as the total count.

       

      Thanks.

        • Re: Count function in straight table
          jagan mohan rao appala

          Hi,

           

          Try like this

           

          For Table1 :

          =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))

           

          For Table 2:

          Count(DISTINCT PO_Num)

           

          Hope this helps you.

           

          Regards,

          Jagan.

            • Re: Count function in straight table

              Thanks, but if I use count(distinct the result will only be 1 record for that PO_Num, but it's supposed to be 5 records

                • Re: Count function in straight table
                  jagan mohan rao appala

                  HI,

                   

                   

                  For this use : =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))

                   

                  PO NumCount (PO_Num)
                  12345

                   

                  For this : Count(DISTINCT PO_Num) - Using distinct you will get 1, I think this is what you are expecting.

                  DatePO NumStoreLine CostUnit CostCount (PO_Num)
                  30/09/141234A543.654.361
                  30/09/141234A543.654.361
                  30/09/141234A265.854.361
                  30/09/141234A265.854.361
                  30/09/141234A1653.254.361

                   

                   

                  Regards,

                  Jagan.

                    • Re: Count function in straight table

                      Thanks, but actually I only want this table:

                       

                      PO NumCount (PO_Num)
                      12345

                       

                      This larger table is only for me to check why the count of PO_Num is 25 instead of 5:

                       

                      DatePO NumStoreLine CostUnit CostCount (PO_Num)
                      30/09/141234A543.654.361
                      30/09/141234A543.654.361
                      30/09/141234A265.854.361
                      30/09/141234A265.854.361
                      30/09/141234A1653.254.361

                       

                      I still don't get my desired table using ' =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))'.

                      Is there any chance you might know why each record for PO_Num '1234' is duplicated 5 times? Because now I am getting this table:


                       

                      PO NumCount (PO_Num)
                      123425


                      And to see why there are 25 records for PO_Num '1234', I did this next table:


                       

                      DatePO NumStoreLine CostUnit CostCount (PO_Num)
                      30/09/141234A543.654.365
                      30/09/141234A543.654.365
                      30/09/141234A265.854.365
                      30/09/141234A265.854.365
                      30/09/141234A1653.254.365


                      Thanks,