6 Replies Latest reply on Jan 21, 2014 8:17 PM by Xiaodong Han

    Store compare to region level result

    Xiaodong Han

      I have a simple data set with store level sales.  I'd like to show store vs. region and national level result in a graph.  I need to show one store result vs. Region.  How can do it?  The sample data is attached.

       

      My real data has thousands of stores.  I don't want to show them all, just want to select one store and show the difference from region and national result.

       

      Thanks,

       

      Xiaodong

        • 1. Re: Store compare to region level result
          Joshua Milligan

          Xiadong Han,

           

          There are 2 primary ways to accomplish this:

          1. Data Blending
          2. Table Calculations

           

          I've attached a workbook to demonstrate the options.  Here's a little explanation.

          1. In the Data Blending example, I've duplicated the data connection (once of Regional Level and once for National Level).  As you click through the connections, you'll notice that I enabled a blend on Month and Region for the regional level and enabled a blend on Month only for the national level.  I renamed the [Sales] field in the copied connections to Regional Average and National Average to make it clear which was which.  The blend allows me to aggregate at different levels of detail, defined by enabling/disabling the various linking fields.
          2. The table calculation uses a single data source and defines two calculated fields -- one for Regional Average and one for National Average.  The two calculations actually use the same code:

           

          WINDOW_AVG(SUM(Sales))
          
          

           

          WINDOW_AVG is a table calculation that gives you the average of an aggregation within a window of your data.  The difference in the two calculations is how I defined the window.  I applied the Regional calculation as Pane Down (right click the field on the Measure Values shelf, and select Compute Using --> Pane Down) as the pane is the region level.  I applied the National calculation as Table Down (as the entire Table down defines the National Average)

           

          The final thing to note with the table calculation is that if I want to filter values, I need to be careful not to use a regular quick filter as that would filter out values that contribute to the Regional/National averages.  Instead, I used a table calculation as a filter (the code was LOOKUP(ATTR(Store), 0) to filter by store only after the WINDOW_AVG calculation had been done.  That makes the 2nd option a little more complex, but it has the advantage of using only one data connection (and only one query to the underlying source -- instead of 3 as the first option -- though if you have thousands of stores, especially hundreds of thousands or millions, 3 small aggregate queries might be more efficient than one query that pulls back hundreds of thousands of records).

           

          I hope that gives you some options.  Feel free to ask any questions you might have!

           

          Regards,

          Joshua

          • 2. Re: Store compare to region level result
            Xiaodong Han

            Joshua:

             

            Thanks you so much for the quick response!  It solved my problem! 

            Xiaodong

            • 3. Re: Store compare to region level result
              Joshua Milligan

              You're welcome!  Just out of curiosity which option did you end up using?

              -Joshua

              • 4. Re: Store compare to region level result
                Xiaodong Han

                Thanks Joshua.  I used option 2.  It’s more dynamic and easy to handle.  Just need to understand the formula better.  I used Window calculation before, never used lookup.  Learned something new.  Is there any document/samples that explain all the function/formulas in detail? 

                 

                 

                 

                Thanks,

                 

                 

                 

                Xiaodong

                 

                 

                 

                 

                 

                Xiaodong Han

                 

                XM Data Strategies, Inc.

                 

                <mailto:xhan@xmdatastrategies.com> xhan@xmdatastrategies.com

                 

                630-638-6688

                 

                Without Data, All we have are opinions

                • 5. Re: Store compare to region level result
                  Joshua Milligan

                  You're welcome!  I'm glad it worked for you.

                   

                  Jonathan Drummey has a list of table calculation resources here that are really good:  Want to Learn Table Calculations? Here’s How! | Drawing with Numbers

                   

                  Regards,

                  Joshua

                  • 6. Re: Store compare to region level result
                    Xiaodong Han

                    Great!  I will do my reading…

                     

                     

                     

                    Again, Thanks for all your help!

                     

                     

                     

                    Xiaodong

                     

                     

                     

                     

                     

                    Xiaodong Han

                     

                    XM Data Strategies, Inc.

                     

                    <mailto:xhan@xmdatastrategies.com> xhan@xmdatastrategies.com

                     

                    630-638-6688

                     

                    Without Data, All we have are opinions