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