I have a list of people. Each person is in this list twice: Once with a “dollars” and a few dollar amounts (for 30, 60, and 90 days) and once with a “quota” for the same periods.
I previously was using two combo boxes in order to get the data I wanted, and it was working fine, but I decided “Hey, why not use a filter?!” which essentially looks like two combo boxes side by side.
Unfortunately, the issue I’ve run into is that it will filter, and then pull the first row that it finds. It will not pull both rows (That is, the quota as well as the dollars). Is there a way to have it return more than one row?
Isn’t that the point of a filter? To pull one row out of multiple rows? How about concatenating each pair of rows together and then using a simple combo box?
LName FName Branch Total/Quota 30 Days 60 Days 90 Days
Smith John Fiji Total 12 24 36
Smith John Fiji Quota 10 20 30
Jones Fred Japan Total 9 18 27
Jones Fred Japan Quota 10 20 30
etc.
etc.
etc.
There are multiple people in each branch. Each person has an Quota and a Total. When you highlight two rows, it will return two different series in a Bar Chart. If I were to concatenate the rows, then I would only have one series, which is not what I need to do.
I know that I can use a filtered rows combo box to separate it out by branch, and then use another one to select the appropriate employee. However, I was looking to reduce the number of actual objects in the dashboard by using a Filter selector, as opposed to two different combo boxes. Unfortunately, when I use “Filter” it only will return the top row of the two (I.E. it only returns the row with “Total,” when I want it to return both rows with the person’s name.
Use an accordion selector with categories for the Branch selection and labels for the sales persons. Use filtered rows for the output. This will select both rows into the target area and you can connect the target area to be consumed by the chart primary and secondary series.
This may not be a feasible fix for your specific case, but i’d highly recommend the “Essentials Bundle” from Centigon as plugins for Xcelsius/Dashboards. This single relatively inexpensive plugin called “Filtered Summary” really changed the possibilities in my Dashboards. I never had to worry about VLookups/Hlookups/Filtered Selectionsand aggregations as these components augmented the dismal filtering and aggregation techniques that the default components provide. Couldn’t be happier.
— Before someone bans this post for propaganda, i must insist its an honest opinion of a good product meant for resolving some of the OP’s problems
In all seriousness, though, I haven’t run into any issues with the normal filtering options. I was only wondering if there was a way to make this work better/more easily.
Depending on the size of the block of data, my first instinct would be to use SUMIF and or some lookups to separate out into two blocks, but then I haven’t had breakfast yet…
Accordions are good too, although I really wish you could add more levels in. I do a lot of locational filtering through 5 levels - but now I just use a location dropdown, get the user to select which one they want and then run a real time query to go fetch the exact data set for that location.
Having said that, if what you’re doing already works, why fix what isn’t broken?
(BOB member since 2005-03-01)
