4 Replies Latest reply: Mar 19, 2013 10:42 AM by David Bykowski RSS

    Same Store - Set Analysis

    Aaron Couron

      I have a situation where I am wanting to compare sales numbers from two different periods, but I only want to include sales for stores that existed in the prior period. The duration of the period and how far back it is are flexible so I must do this in the front-end.


      Year Store Sales

      2010 123 10,000

      2011 123 10,000

      2011 456 15,000

      Store Sales 2010: 10,000

      Same Store Sales 2011: 10,000

      Any thoughts?


        • Same Store - Set Analysis
          Erich Shiino

          You need set analysis with advanced searches.

          From the help, you need something similar to the second example:

          Set Modifiers with advanced searches

          Advanced searches using wildcards and aggregations can be used to define sets.


          sum( {$-1<Product = {"*Internal*", "*Domestic*"}>} Sales )
          returns the sales for current selection, excluding transactions pertaining to products with the string 'Internal' or 'Domestic' in the product name.

          sum( {$<Customer = {"=Sum({1<Year = {2007}>} Sales ) > 1000000"}>} Sales )
          returns the sales for current selection, but with a new selection in the "Customer" field: only customers who during 2007 had a total sales of more than 1000000.

          You can use something like:

          sum( {$<Store= {"=Sum({1<Year = {2010}>} Sales ) > 1000000"}>} Sales )

            • Same Store - Set Analysis
              Aaron Couron

              Thanks Erich,


              That was the boost I needed. I am ending up with something like:



              So it will only include the stores that had sales greater than 0 in the previous period.

              Thanks again for quick response. Awesome.


            • Re: Same Store - Set Analysis


              I've been able to use this post to get very close to the solution I'm looking for - but I'm not quite there yet.  I have a chart that has dimensions of Year and expressions of [Comparison Date], [All Store Sales], and [Same Store Sales] that I am attempting to get this to work in.   I have a calendar object, that when a date is selected, the previous few years are displayed along with their corresponding [All Store Sales] and [Comparison Date] which is a variable found with the following:


              only({$<Weekday={$(=$(vSelectedDate_Weekday))}, WeekNumber={$(=$(vSelectedDate_Week))}, Date=, Store=, Year = {"<=$(vSelectedDate_Year)"}>} Date)


              The variables within that variable are simply retrieving the weekday, week, and year of the selected date in the calendar object.  The comparison date is the date we're interested in for same store sales since it's the same day of week in the same week for the year prior.  I've tried several different instances to get the expression [Same Store Sales] to work but haven't had any luck.  However, I can get it to work if I hardcode a date into the expression like the following:




              That expression will return a value for the top row as shown below:


              Same Store Comparison for Sunday - 3/10/2013  

              Year    Comparison Date    All Stores Sales    Same Store Sales

              2013    3/10/2013              100,000.00              95,000.00

              2012    3/11/2012              85,123.00                0.00

              2011    3/13/2011              80,200.00                0.00

              2010    3/14/2010              63,600.00                0.00


              I'd like to be able to somehow reference the [Comparison Date] column (which is = $(vComparsonDate)) when calculating my [Same Store Sales].  I've tried that in many ways including the following but haven't had any success:




              I've tried several different syntaxes as I've found several set analysis examples referencing variables but haven't had any luck.  I've also tried to convert the Date column and vComparisonDate to number columns and had the same results.


              Can anyone offer any suggestions?  Thanks in advance.

                • Re: Same Store - Set Analysis

                  Perhaps I should try and simplify my question from above.  Above, Erich and Aaron cite a couple examples of using Set Modifiers with Advanced searches.  I've been able to get this to work as well, but only if I hardcode a value in my set analysis.  Is there anyway to use a Set Analysis with Advanced searches with a variable such as the following:




                  In this case the variable vComparisonDate is a single value in the format '3/15/2013'.  I've also tried this using a num as well without any luck.  Am I trying to accomplish something that isn't possible with this syntax?