Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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

 

View solution in original post

9 Replies
thomas_skariah
Creator III
Creator III

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_Michaelides
Luminary Alumni
Luminary Alumni

Try:

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

Hope this helps,

Jason

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

 

Anonymous
Not applicable
Author

I have to find count not Sum....

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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

and J thanks for guiding me.....

U guys rocks

Anonymous
Not applicable
Author

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_Michaelides
Luminary Alumni
Luminary Alumni

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