topic Re: Grouping Data and Counting. in QlikView

Grouping Data and Counting.

Wed, 08 May 2013 08:10:11 GMT

I am new to Qlikview. I am trying to accomplish the following.

I have about 25,000 users and their monthly spending. I have grouped then as following

I have created a List box with Month.I have one char which displays Month wise total number of usesr who uses credit card.

For the above char I used the following: Dimension: YYMONTH

Experession Count (DISTINCT USER_ID)

Now I want to group them

I have a field called CHARGED_AMOUNT

Count number of users who spent <1000

Count number of Users who spent 1000-4999

Count number of users who spent 5000-9999

Count number of users who spent 10000-19999

Count number of users who spent >20000

How do I count?

Thank you

Re: Grouping Data and Counting.

Wed, 08 May 2013 08:26:29 GMT

Hi,

Try the following expression(s):

Count ({<Amount={'<1000'}>} DISTINCT USER_ID)

Count ({<Amount={'>=1000<5000'}>} DISTINCT USER_ID) etc

Re: Grouping Data and Counting.

Wed, 08 May 2013 08:50:25 GMT

HI cchhatralia

Thanks for the reply. I did as follows

DIMENSION: CREDIT_CARD_MODEL (we have only one credit card model

Experession: Count ({<CHARGED_AMOUNT={'<1000'}>} USER_ID)

But the outcome is slightly different. It calculates number of times the user spend less then 1000. Since I have 5 months data and the user spent every month less then 1000 for the past 5 months it calculates 5.

See the image

I can add additional dimension. but looks like the result I am expecting is different

I want First coulum Total number of User who spent < 1000. Remember I have a list box which contains months. We have 5 months of data. So the user may spent each month some amount. I can select YYMM from my listbox which will narrowdown on month basis.

Re: Grouping Data and Counting.

Wed, 08 May 2013 08:57:46 GMT

are you able to post a sample doc?

Re: Grouping Data and Counting.

Wed, 08 May 2013 09:11:31 GMT

Sure. Thanks I have attached a dummy data file here

Re: Grouping Data and Counting.

kumarnatarajan — Wed, 08 May 2013 09:17:16 GMT

Hi,

You cat able to use Class() function

Ex : http://community.qlik.com/thread/65352

Else

You cat able to use Dual() function and if() Condition

EX : http://community.qlik.com/thread/4328

Else you want more details share Sample. I can help you

Re: Grouping Data and Counting.

Wed, 08 May 2013 09:29:58 GMT

See the attached. Is this what you are after?

(The bands are respective of what you have above.)

Re: Grouping Data and Counting.

Wed, 08 May 2013 09:38:16 GMT

Thank you cchhatralia. Great Nice of you helping me..