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

    calculate with count


      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



          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





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




                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,



                     category as category2

                from ...


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


                count the duplicated script