7 Replies Latest reply on Sep 29, 2015 1:23 AM by Simon Runc

    Compare one customer with other subset of customers

    pulkit jain

      Hi All,



      I am trying to compare one selected customer to other customer subset for example:

      If table have following values:

      Customer   | Amount

      A               | 10

      B               | 30

      C               | 20

      D               | 45

      E               | 42



      I want to see a pie chart between sales of 'A' with combined sales of  'C' and 'D'.

      But user should be able to make this selection on runtime.

      Kindly provide any suggestions.

        • 1. Re: Compare one customer with other subset of customers
          Simon Runc

          hi Pulkit,


          Just to ensure I fully understand your requirements, I have a couple of questions!!


          How do you see the end user selecting the 'A' Group?, via a Parameter? and if so are you able to manually update the list of customers? [currently parameters aren't dynamic, we can set them up from a field, but this won't change if there are new customers once the data refreshes...you need to clear it manually and then re-populate]


          How many combined sales ('C' and 'D' in your example) can the user select 2, or any number?


          Just thinking about what solution will be best. eg. If they can select any number of customers for 'combined sales', parameters won't really work as they can only be single select, so you'd need one for each!

          • 2. Re: Compare one customer with other subset of customers
            pulkit jain

            Hi Simon,


            Your question are right on point...Actually I have a solution by using Parameter for selecting 'A' and multi-select filter for set(like 'B' and 'C'), but as you point out it has to be manually updated..which is not feasible for me as changes are very frequent. That is why I posted here to see if someone can propose any other approach.

            For your second question, there is not a fix number for combining the customer column,it could be any number of customer.

            • 3. Re: Compare one customer with other subset of customers
              Simon Runc

              Yes the lack of dynamically updating parameters is very frustrating (especially since you can populate them from a field yourself...why can't it just 'run that function on all Parameters when data is refreshed'!!)


              The only way I know round this would be to use a Blend, similar to this article




              do you have the flexibility to create a second data source? This would only need to be a Unique List of the Customers (some CustomSQL would do the trick, or if it's already from a Database you could easily create a view and join to that).


              Let me know if you can, and I'll see if I can get a Blended version working, if not I'll get my thinking cap on!!

              • 4. Re: Compare one customer with other subset of customers
                pulkit jain

                Yes it is already created on a database and I can create custom tables if required.

                • 5. Re: Compare one customer with other subset of customers
                  Simon Runc

                  hi Pulkit,


                  OK I've got a 'kind-of' solution using this technique...I'm still trying to crack the last bit, but thought I'd share where I am!


                  Attached is the workbook, and Excel Spreadsheet (this has 2 tabs, one for each of the data sources with the 'ViewReturningCustomers' tab being the new datasource simply being a list of customers - the bit we'd like the dynamic parameter to do!)


                  So the first part, is to create a single blend field, in both datasources. I've called my '1One' and is simply the number '1'


                  Next I create the following calculated field


                  IF MIN([Customer]) == ATTR([ViewReturningCustomers (Dynamic Parameter with Blend)].[Customer]) THEN

                      "Selected Customer"





                  as you'll notice this is exactly the same as the link I posted!


                  I can then bring in the 'Customer' from our secondary data source as a filter, and with customer (from our primary datasource) in the Viz this is recalculated based on the selection. It's important to ensure the secondary source is only blended on the '1One' field and not the customer too. I think the 'working' tab shows what's going on here the clearest!


                  I then create 2 calculated fields for 'Other' [Combined Amount]


                  WINDOW_SUM(IIF([Selected Customer]='Other',(SUM([Amount])),0))


                  and the reverse for the selected customer [Selected Amount]


                  WINDOW_SUM(IIF([Selected Customer]='Selected Customer',(SUM([Amount])),0))


                  I then bring these into the view and set both to Compute using customer. So far so good!! and you'll see the numbers are affected by the selection in the 2 filters (btw the second filter is customer from the primary datasource, where the user selected the 'combined' group).


                  Now things get a little more complicated in a Pie! as we have to have Customer in our VizLoD (as we are blending, all blended fields are brought in as an Aggregate, which is why we use MIN([customer]) in the first calculated field). If we don't have it there the MIN([customer]) won't compute properly. Now the issue with having Customer in our VizLoD is that we get a 'mark' for each customer. To get round that I've brought an index(), compute using customer, on the filter shelf and set to 1. This creates a Table Calc filter, which due to the 'order of operations' has the affect of filtering out all but 1 of the marks, without filtering the underlying data.


                  The place I'm currently stuck...and it might well be an 'end of the day' thing!! is to not have the 'Selected Customer' in the 'Combined List'. also you'll notice that the Selected customer needs to be selected in the primary source filter too.


                  I'll have a think tonight, about the last bits, and drop you a note tomorrow (kind of feel I've done the hard bit!! but can't get the last bit working!!). Also let me know if any of the above doesn't make sense

                  • 6. Re: Compare one customer with other subset of customers
                    pulkit jain

                    Its Working perfect...Thanks a lot...

                    • 7. Re: Compare one customer with other subset of customers
                      Simon Runc

                      Excellent news!...it does get a bit fiddly! (to use the technical term!!) but is the only way round this until we get our 'Dynamic Parameters'!