Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Try:
Count({<[Account Name]={"=Sum([Product bookings net])>'$(vPickup)'"}>} [Account Name])
Hope this helps,
Jason
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
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
I have to find count not Sum....
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.
Hey thanks alot alot niro it worked.....
and J thanks for guiding me.....
U guys rocks
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
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