Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

shashank20
New Contributor III

I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Hi Masterminds,

I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

I have used all possible approaches including sets

=count(

{<

[Product Bookings Net] =

({'$(=vSum)'} * {'>=$(=vPickup)'})

>}[Account Name])

where vSum = aggr(Sum([Product Bookings Net]),[Account Name])

and vPick= the value entered by user

but its not working

please reply fast I have to submit it today

Thanks in advance

1 Solution

Accepted Solutions
morris70
Valued Contributor

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Would this help? =Sum(if(Aggr(sum([Product Bookings Net]),[Account Name] )>50000,1)) Of course you would replace the 50 000 with a variable

 

9 Replies
thomas_skariah
Contributor III

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Hi Shashank,

Try this code =count(if(Amount>vtest,Acc_ID))..with vtest consider as a variable for Input box.

Not sure whether you are looking for this.

Regards,

Tom

jason_michaelid
Honored Contributor II

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Try:

Count({<[Account Name]={"=Sum([Product bookings net])>'$(vPickup)'"}>} [Account Name])

Hope this helps,

Jason

shashank20
New Contributor III

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Hi Jason,

It wont work as I have to aggregate the sum([Products Booking Net]) with dimension Account name

The solution u gave will take whole sum of Products Booking Net and it then checks accordingly

I have to do something like this

Lets say after aggregating with each account name and putting input value as 5000000 data will be like

Account Name Product Bookings Net
SOUTHERN CALIFORNIA EDISON 8034325.35
DIRECTORY OF REALESTATE-PENSION FUND 7050000
SOCIAL SECURITY ADMINISTRATION 15070000
SCHLUMBERGER LIMITED, INC 6000000
HCA INC 32968190.56
National Grid 8600000
LUXOTTICA GROUP RETAIL 18475000
SOUTHERN COMPANY 11440000
CBP-Customs and Border Protection: DHS 5320000
microsoft 17000000
USMC 6056656.8
LOWE'S Inc. 8070000
GOV. PERNAMBUCO - ATI 18000000
COMCOR MOSCOW TELECOMMUNICATION CORPORATION OAO 10547563
DISTI STOCK 135980701.7
MTN NETWORK SOLUTIONS 9429986.82
FORD MOTOR COMPANY 8465618.853
MGTS 8635795.8
FORT WORTH ISD 8486726.08
ZAO REGIONAL INFORMATION NETWORK 11847440.61
WALGREEN CO. 5809223.7
NORTHROP GRUMMAN SPACE & MISSION SYSTEMS CORP. 6129756
BC HYDRO POWER & AUTHORITY 11669880.48
HARRAH'S ENTERTAINMENT INC 8166168.75
NORTHROP GRUMMAN SYSTEMS CORPORATION 9787649
7-ELEVEN INC 11328336.18

In the above chart I have used the formula =if(Sum([Product Bookings Net])>=$(vPickup),SUM([Product Bookings Net]))

as u see in the above table all the values are greater then 5000000

so in text box the count that should be displayed as 26.

Please let me know if ur able to do this, il be thankful to u.

Thanks

S

morris70
Valued Contributor

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Would this help? =Sum(if(Aggr(sum([Product Bookings Net]),[Account Name] )>50000,1)) Of course you would replace the 50 000 with a variable

 

shashank20
New Contributor III

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

I have to find count not Sum....

jason_michaelid
Honored Contributor II

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Have you tried niromo70's solution?  It looks at first glance like a Sum(), but actually every [Account Name] with a Sum(Product Bookings Net) > 50000 is being given a value of 1, which is then summed.  Should give the same result as Count(), but using Sum() is more efficient.

shashank20
New Contributor III

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Hey thanks alot alot niro it worked.....

and J thanks for guiding me.....

U guys rocks

shashank20
New Contributor III

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Hey guys the way u helped me in finding the count can u please help me in same way finding the average of the product bookings net of those accounts whose sum(product bookings net) is greater then the value inserted by the user in input box

thanks in advance

jason_michaelid
Honored Contributor II

Re: I have a text box where in I have to show the count of the account number whose sum(Product bookings net ) is greater then the number that user entered into the input object

Just replace the Sum with Avg, and the 1 with your field name:

=Avg(if(Aggr(sum([Product Bookings Net]),[Account Name] )>50000,[Product Bookings Net]))

Hope this helps,

Jason

Community Browser