3 Replies Latest reply: Mar 11, 2011 4:44 PM by John Witherspoon RSS

    sales detail and sales total in one chart

      Hi Community,

      I have created a document to track sales by salesman by customer. It is as simple as multiple buttons representing each salesman and a table chart with product as a dimension and customers as columns. (sum(sales) as the expression. When the user clicks on a specific salesman button, their customers appear in the chart.

      My issue is I sometimes have multiple locations for a customer and want to track each location seperately or only a specific location and for others, i have multiple locations but am only intersted in the total (not broken down by location). I want my table to be able to show either by location or the sum of the location all at once.

      Currently with the button i use the select in field and the search string is either the customers number-location or number-* (to select all locations). How can i make it so that when it is number-* it is the sum of the locations. Or is there a better way then number-*?

      Anybody have any ideas? Let me know if you need clarification, thanks!

        • sales detail and sales total in one chart


          The table could have customer ABC-loc 1, DEF (total), GHI-loc 5, GHI-loc 6, JKL (total).

          If I am showing the total, I would not show the location details for that customer. But i wanted this to all be in one chart.

              • sales detail and sales total in one chart
                John Witherspoon

                We use two fields, "Customer" and "Customer Location". If I want a mixture in a chart, I use a pivot table and let you expand a customer to see the locations. That way, the users can control which customers are and are not expanded.

                If how you want to view it is instead always fixed, you could handle it with a "Customer Location Group" field, though probably with a better name of some sort. Based on your example, you could make a table like this. You could use a mapping load or left join if you wanted it on the same table as your other data:

                Customer Location, Customer Location Group
                ABC-loc 1, ABC-loc 1
                DEF-loc 2, DEF (total)
                DEF-loc 3, DEF (total)
                DEF-loc 4, DEF (total)
                GHI-loc 5, GHI-loc 5
                GHI-loc 6, GHI-loc 6
                JKL-loc 7, JKL (total)
                JKL-loc 8, JKL (total)

                Now just use Customer Location Group as your dimension instead of Customer Location, and it will group the locations for some customers, but not other, all under your script control.

                I'm not sure I entirely understood, though, particularly what you're doing with buttons. Generally, if you want to be able to select different sales people, you'd just use a list box or multi-box. I'm also not sure what you're talking about with the search string on customers for a button. Is that some other set of buttons than the sales people? And again, why not just use list boxes or multi-boxes to do selections, like the product is designed for? Why make the users learn yet another way to make selections, specific to this one application?