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")
Stephen apparently had the same suspicion as I - you can't use set analysis here. It works on global level, not per dimension value. So, you have to use "if" instead of set analysis here.
As for the numbers, I can't guess without application. You'll have to figure out...
