Anybody know how to write a query into a pivot table while sorting data ? My data includes holdings as of a specific month for a specific portfolio and the data that i have shows the same security multiple times when/if there have been a few trades on that security during the month . When I put this info/data into a pivot table its shows the security with a count of 2 ( 2 trades in the month ) . Can I write a query to reflect the security only once ( if it is held as of mth end ) ? All I need to know is if the securoty was held or not within the portfolio as of mth end , I dont care how many times its been traded during the month .
I’m not sure I completely understand the question - why don’t you just not drop anything into the “data” area of the pivot table, resulting in a list of just the securities in the data set?
This can be done straight from the data much easier than in a pivot table. Select DISTINCT(security_name) from where trade_date between and This query will give you a list of all securities traded during a particular set of dates.
Young_Prof Wrote: ------------------------------------------------------- > I’m not sure I completely understand the question > - why don’t you just not drop anything into the > “data” area of the pivot table, resulting in a > list of just the securities in the data set? when i drop the data i.e the security names - if the security appears more than once it assigns a count of 2 to that security
darkhelmet Wrote: ------------------------------------------------------- > This can be done straight from the data much > easier than in a pivot table. > > Select DISTINCT(security_name) > from > where trade_date between and > > This query will give you a list of all securities > traded during a particular set of dates. I select DISTINCT names for a the security description column in my data ? is that what you mean ? I’m trying to compare the holdings of 2 portfolios over certain ponts in time .
I meant that you should drop it in the “drop row fields here” section, not the “drop data items here” section…but maybe it’s more complicated than that, let me know if it is darkhelmet is talkin SQL, maybe he thinks you’re in access
I think he means select as in a SQL Select Query. The excel equivalent would be to sort your data for unique records prior to dumping into a pivot table.
alpha bound --how exactly do i do this ? just use the DATA -sort function ??
data - filter - advanced filter - click unique items only (alt d f a, then click unique)
all that seems to do is hide the double counted securities . When i put it in to my pivot table it still shows a count of 2 .
Rudeboi Wrote: ------------------------------------------------------- > all that seems to do is hide the double counted > securities . When i put it in to my pivot table it > still shows a count of 2 . after filtering, copy/paste the filtered data on another tab and create your pivot table from there.
yes… i’m talking sql, but this is really easy to do in excel as well as in access. There really isn’t any way to get it to come up in a pivot table with a count of 1 without doing a fair bit of massaging of the data first. Much easier just to reference your tables and run a query. If this is on-going, you should really build an access dbase for the purpose and run the query any time you want.