4 Replies Latest reply: Apr 3, 2015 2:06 PM by Sunny Talwar RSS

    Set Analysis - outside of filter?

    Matt Mohon

      So I'm summing sales of a unit and comparing it to their prior year's sales.  I'm doing this in a text paragraph all concatenated together, it's the sort of thing my users love.  Example (Orange is calculated off fields):

       

      "Unit A had sales of $3,000 ytd for 1Q 2014.  For the same period in 2013, Unit A had sales of $2,500.  The percentage change from period to period was 20%."

       

      I've declared a vMaxYear, vPrevYear, vMaxQtr to derive from the filter.

      Num(sum({$<year = {$(vMaxYear)}, qtr = {'<=$(=vQtrMax)'} > } [Sales]),$'#,##0 ')  = $3000

      Num(sum({$<year = {$(vPrevYear)}, qtr = {'<=$(=vQtrMax)'} > } [Sales]),$'#,##0 ')  = $3000

       

      This is all predicated that my filters are simplistic.  I can only choose 1 Unit, and the paragraph will always show the maximum year. I want to add other text that takes into account data "outside" of the filter.  So in this case, I'm filtered to Unit A, but I want to compare Unit A's sales to that of Unit B, C, D, etc.  I'd like to say

       

      Unit A's sales of $3,000 was 25% of the total market which had reported sales of $12,000.

       

      Is there a way to do a set analysis that looks outside of the filter parameters and takes into account the other Unit's?  That way I can calculate the 25% & the $12,000 in my example above?