9 Replies Latest reply: May 14, 2012 6:41 PM by Jorge Villalobos RSS

    calculate with count

    pkrolicki

      Hello ,

       

      i have a simple straight table with dimension customer and a calculated category (i can't use the category in dimension):

       

      customer     category

      1                    A    

      2                    A    

      3                    B

      4                    C

      5                    C

       

      i want smiply count the category for example category A = 2 but it doesn't work with count(category)..

       

      any idea?

        • calculate with count
          Alexis Tan

          Hi,

           

          In your example

           

          if your dimension is customer then you count(category) your count will always be = 1^^

           

          because expression will base its grouping from your dimension. and since your dimension (customer) has a unique data then your count will always be 1.

           

          customer     category     count(category)

          1                    A                        1

          2                    A                        1

          3                    B                        1

          4                    C                        1

          5                    C                        1

           

           

          Regards,

          Alex

          • Re: calculate with count
            Sunil Kumar Chauhan

            see the attched file

             

            hope this helps

              • Re: calculate with count
                Dennis Hoogenboom

                Hi there,

                 

                If Sunil's idea is not working for you, (you say you can't use category as a dimension) you could try to add a counter in your script which create a counted total in one field in your script.

                 

                I added Sunil's QV-document to clearify what I mean:

                 

                 

                t:

                Load '1' as counter,

                          * ;

                Load * inline [

                customer   ,  category

                1          ,  A   

                2              ,      A   

                3             ,       B

                4             ,       C

                5             ,       C

                ];

                 

                 

                Join (t)

                Load category,

                          Sum(counter) as CountCategory

                Resident t

                Group by category;

                 

                drop field counter from t;

                 

                 

                This will add a information field CountCategory which you can use (dont use it as a sum() )

              • Re: calculate with count
                Jorge Villalobos

                In your load script create a duplicated field based on your category,

                 

                Load

                     category as category2

                from ...

                 

                Apply any transformation from the data of that field that may benefit your application performance.

                 

                count the duplicated script

                 

                count(category2)