5 Replies Latest reply: Aug 10, 2012 4:11 PM by Dimitris Charalampou RSS

    how to Group Data

      Hi there. I have a list with all my customers , their ids and their info. I also have some data like the ones in the included excel. I want to create a chart that will show me how many customers have 0 transactions , how many 1 transaction, how many two, etc etc.

       

      I think it is quite simole for someone with more experience but i am new in qlikview so guys any help would be very appreciated!!!

      Thanks                             

        • Re: how to Group Data
          Jonathan Shaltz

          Your Excel file seems to just have two columns, customer ID and transaction amount.  What have you tried?  Hint: pivot tables are considered a type of chart in QlikView.

            • Re: how to Group Data
              Manoj Kumar Varatharajan

              Attached is the Test.qvw scripted understanding your requirement. I had included a dimension table in Sheet 2 and a flag in my script. Please check if it helps.

               

              In ideal situation, you should generate a Transaction ID (combination of date, customer, product) and join them the Calendar, Customer and Product table to view based on the slicing requirements.

              • Re: how to Group Data

                i tried  using sum and count but with no luck. My excel file has only two columns because that information is sufficient to explain what i want to do. I want to get something like this:

                 

                Number of transactions     Number of Merchants

                         0                                         0

                          1                                         5

                          2                                        10

                          3                                         6

                          .                                          .

                         .                                          ,

                          .                                          .

                  • Re: how to Group Data
                    Jonathan Shaltz

                    That is a little more complicated; based on your original message, it sounded like you wanted a list of customers and the number of transactions for each.

                     

                    Using Manoj's test data, I believe you need a straight table with a calculated dimension:

                    =Aggr(Count([transaction amount]), [customer id])

                     

                    And an expression:

                    =Count(distinct [customer id])