2 Replies Latest reply on Mar 25, 2016 7:34 AM by Tai Abukasis

    Issue creating stacked bar with blended data

    Tai Abukasis

      Hey Everyone,

       

      I've blended two data sources (on Division) and I'm looking to create a band calculation for count of opportunities that were pushed into another Quarter.

       

      The aggregate measure for this comes from the secondary data source, and the calc within the primary data source is:

       

      if count([sfdc_event_tracker.[Qtrs Pushed])< 0 THEN "NULL"

      ELSEIF  count([sfdc_event_tracker.[Qtrs Pushed]) = 1 THEN '1 Quarter'

      ELSEIF count([sfdc_event_tracker.[Qtrs Pushed]) = 2 THEN '2 Quarters'

      ELSEIF  count([sfdc_event_tracker.[Qtrs Pushed]) = 3 THEN '3 Quarters'

      ELSEIF count([sfdc_event_tracker.[Qtrs Pushed])  = 4 THEN '1 Year'

      ELSEIF  count([sfdc_event_tracker.[Qtrs Pushed]) > 4 THEN '1 Year +'

      END

       

      The ask was to view this breakdown as a stacked bar by dragging the calc into the color shelf. This works perfectly when I don't blend the data.

       

      Please see below picture which shows what I get when the data is blended (i.e. calc isn't being broken out):

      Capture2.PNG

      I thought about using LOD but saw that you can't use this when blending data.


      Didn't know if there were any workarounds that I wasn't familiar with or if I'm missing something all together.

       

      Please let me know if there are any questions.

       

      Thanks,
      Tai

        • 1. Re: Issue creating stacked bar with blended data
          Jonathan Drummey

          Hello Tai,

           

          Your screenshot did not include the Rows and Columns Shelves, I'm guessing there's a single continuous (green) measure pill on the Columns Shelf, is that correct?

           

          Assuming so, what you're running into is a fundamental constraint of data blending. The results of data blending are always aggregated to the viz level of detail. The viz level of detail is the entire data set, so count([sfdc_event_tracker.[Qtrs Pushed]) is returning some number > 4 and you're getting the 1 Year + result.

           

          Without knowing more about your data I can't be sure of what to recommend, my guess is that there's an implicit dimension that you want the count() to be grouped by, in that case for the data blend to work you'll need to make sure that dimension is in the view and it will create the partitioning necessary for the data blend to work.

           

          Jonathan

          • 2. Re: Issue creating stacked bar with blended data
            Tai Abukasis

            Hey Jonathan,

             

            Apologies for not including the Shelves in the view. You are correct in your assumption.

             

            I figured this was one of the fundamental constraints (as you say), but wasn't sure if there was some sort of workaround that I wasn't familiar with.

             

            Thought about using a partitioned view, but ran into the issue of having to bring the field in as an attribute (rather than an aggregate) and therefore returning an asterisk due to the one-to-many relationship (though that could just be user error on my part).

             

            Thanks very much for the help and for confirming this information.

             

            Also, big fan of Drawing with Numbers, so thanks for all of the great content on there as well.

             

            Tai