I really need help with a query I need to create. The data I have currently has an clientid, a service to which he in linked, either with an unassigned value or an active value, and then the current broker , example
client1, insurance, active, broker1
client1, medical, unassigned, null
client1, investments, active, broker2
We are bringing this through on a bar chart, the issue now is that where the 3rd column is unassigned, we want to show all possible brokers linked to a client, ie have broker1 and broker2 showing on a bar chart as possible brokers for the unassigned service. What is the best way to do this?
The bar chart would a stacked bar chart, with a count of all clients by the third column. When I build in a drill on the bar chart it drills down when you click on active,but not for assigned. I managed to figure it out though. I populated the last column to give through the values of all brokers that can link to the unassigned clients. Now I can drill down, also using a count distinct on the client id, Now I can see a count of all distinct client that are in a=unassigned status, and then I drill down to the individual broker, there is also a count distinct on the client for the broker selected.It took me a while to get the information in the right format as the original data does not have the information as I need it .
