10 Replies Latest reply: Jan 7, 2013 2:49 PM by Michael Solomovich RSS

    Set Analysis: Search String / concatenate dimensions

      My dataset has Group, Month (YYYYMM), Year (YYYY), Renewal Month (MM), and Sales.


      I want to make a table with Group and Year as the dimension. The two expressions would be "sum of sales at the Renewal Month" and "sum of sales at the month before the renewal month".


      I'm just trying to work on "sum of sales at the Renewal Month" for now by using Set Analysis, but haven't had much luck.


      For example, I've tried =sum({<Month={"='2012' & [Renewal Month]">} Sales). It seems that the concatenation is not working in the search string, as I get the equivalent of =sum(Sales) with this expression.  


      (Ultimately, 2012 would be the Year variable and there'd have to be some logic for if renewal month is 01, then go back one year and pick "(Year -1)12")


      Sorry, I'm not able to upload the file.


      Thanks for any help on this.