topic Re: How to use Count in Piviot table in QlikView

How to use Count in Piviot table

Mon, 03 Jun 2013 04:36:56 GMT

Hi guys,

I want to count the no of customers whos amount is between 25 lac - 30 lac in pivot table.
As in pivot table the same customer having 10 trasactions are agg in one.
ex- Suppose company A has 3 transactions (5,10,and 15) so its amount will be 30.

Company B has 5 transactions (2,4,5,6,8) so its amount will be 25.

Company C has 4 transactions (2,4,5,6) so its amount will be 17.

So from above i want to count the total no of company who's amount between >=25 and <=30..

Hence we waant to find out the distinct customers whos transaction is between >=25 and <=30.

As i am using personal edition plz attach the word file.

Re: How to use Count in Piviot table

Mon, 03 Jun 2013 04:45:46 GMT

form the buckets in the edit script or expression level,

If ( Count(transactions)>=25 and Count(transactions)<=30,'Between 25 - 30',

If ( Count(transactions)>=30 and Count(transactions)<=45,'Between 30 - 45',' Above 45 ')) as Buckets

Re: How to use Count in Piviot table

Mon, 03 Jun 2013 05:20:25 GMT

Hi prashant,
Thanks for your reply.
but I dont know how to create bucket and where to write the expression.
please tell me in detail how to overcome from this problem.

Re: How to use Count in Piviot table

er_mohit — Mon, 03 Jun 2013 05:34:11 GMT

Try this in script where you load your table and field Transaction and customer there you put this code and use the field in pivot table

Rangeonly(If ( transactions>=25 and transactions<=30,Customer)) as 'Between 25 - 30'

hope it helps

Re: How to use Count in Piviot table

khadeer — Mon, 03 Jun 2013 05:34:29 GMT

Hi,

You can overcome this in 2 ways,

You have to create bucket in backend like what prashanth told and name as new field ... and plot that new feld in front-end based on requirement. Else write same in if condition in expression.

Hope it helps you.

Regards,

Khadeer

Re: How to use Count in Piviot table

Mon, 03 Jun 2013 06:01:07 GMT

hey guys,

i am using following expression in load script.

If ( Count(amount)>=200000 and Count(amount)<=300000,'Between 200000 - 300000',' Above 300000 ') as bucket

and in pivot table i am using dimensions customername ,and amount ...

in expression i am using sum(amount)

so my pivot table shows three columns customername , amount and sum(amount)...
so depending on sum(amount) i want to use the bucket....

i done with bucket in script ...it is giving me list box named as Bucket...but when i check on the value it is not making any changes....mit be bcz i want values to be in sum(amount ) column...