Set Analysis: Search String / concatenate dimensions

[#1]

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.

[Anonymous]

If your field Month is text, and has format like '2012Jan', and [Renual Month] has format like 'Jan', then this should work:

=sum({<Month={"$(='2012' & [Renewal Month])">} Sales).

Otherwise, to help you I need to know the exact format of your data.

Regards,

Michael

[#3]

Month is a number. For example, 201202. Renewal month is text, 02. The concatenation is going to be text then. What's the way to cast the concatenation into a number?

[Anonymous]

In this case my expression should work as well.  If it still doesn't, try add num#():

=sum({<Month={"$(=num#('2012' & [Renewal Month]))">} Sales)

[#5]

Hmmm.. That isn't doing the trick. Each row gets value of 0.

(I noticed a "}" is missing from your expression, but that didn't fix it)

The fields are part of a bigger dataset. Group and Renewal Month are on one table, Sales and Month are on their own tables. There is a Linker table that joins these fields and this model has been used and tested for a while, so the joining is working. Perhaps in this case it is prohibiting this expression from working?

[Anonymous]

May I see the fixed expression?  Want to make sure where did you add the missing "}".

I don't think that the structure you described is a problem.  Most likley something is missing in this expression.  It would be helpful to see an example - reduced file with scrambled data.

[#7]

sum({<Month={"$(=num#('2012'& [Renewal Month]))"}>}Sales) 

[swuehl]

Is [Renewal Month] unambiguous in global context?

I mean, it is not depending on Group or Year, and you get a value back if you input =[Renewal Month] in a text box?

[Anonymous]

Yes, this looks right.  Waht if you replace set with if:

sum(if(Month=num#('2012'& [Renewal Month]), Sales))

[#10]

Swuehl: Each Group is assigned exactly one Renewal Month and this is consistent from year to year

Michael: Your expression got a non-zero answer for most groups, but the renewal month sales is greater than total sales for some, so some double counting taking place. I will try to dig into more, but wondering if you know why.

Group ID	Total Sales	Renewal Month Sales
1	103,654	261,281
2	27,609	127,403
3	32,804	156,250
4	20,952	60,761
5	231,351	350,640
6	59,243	55,456
7	5,401	0
8	44,378	33,505
9	44,420	42,219
10	57,766	0
11	147,699	142,240
12	22,133	0
13	115,023	0
14	880,228	748,088
15	125,711	0
16	636,693	607,004
17	83,834	71,196
18	164,377	152,704
19	259,879	259,344
20	193,482	192,274
21	60,217	58,725
22	172,322	160,515
23	95,514	83,730
24	2,280,004	1,905,258
25	23,474	15,246
26	663,501	853,616