topic Re: how to Group Data in QlikView

how to Group Data

Fri, 10 Aug 2012 18:43:18 GMT

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

Fri, 10 Aug 2012 19:32:57 GMT

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

manojkvrajan — Fri, 10 Aug 2012 19:42:30 GMT

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

Fri, 10 Aug 2012 19:44:13 GMT

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

Fri, 10 Aug 2012 19:49:12 GMT

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])

Re: how to Group Data

Fri, 10 Aug 2012 20:11:34 GMT

Thank you guys for your quick replies!!!

What Jonathan proposed worked fine.

Manoj's awswer is a bit more complex but it showed another way of showing what i want. i might use it in another project of mine...

You helped me a lot!!!