Set analysis expression column refers to different dimension value

[#1]

Hi all,

I have little problem with set analysis expression. I´m trying to achieve a chart with columns which refers to different dimension value than showing on dimension column and I got it partly working but not totally correct.

Here is example, let´s say that I have 6 stores (A,B,C, A1, B1, C1) and I have to calculate sales of store A but at the same row, I have to show A1 sales also, something like that:

	Sales	Additional sales	Total sales
AStore	100	25	125
BStore	50	15	65
CStore	75	10	85
		This column should contain sales as follows: AStore line Additional sales = sales of A1Store BStore line Additional sales = sales of B1Store CStore line Additional sales = sales of C1Store

I wrote expression

if(WildMatch(StoreName,'AS*'), Sum({<#Store={'A1'}>} total Sales),

if(WildMatch(StoreName,'BS*'), Sum({<#Store={'B1'}>} total Sales),

if(WildMatch(StoreName,'CS*'), Sum({<#Store={'C1'}>} total Sales)))

And it worked fine until I had to add another dimension (Time cyclic group = Year/Quarter/Month...). After that, each row shows total sales for selected time period, not sales by month (for example). If I remove total from expression, I got 0 as result.

Is there any way I can accomplish that kind of chart?

[swuehl · Accepted solution]

Maybe add the field list of all fields in your dimension group to the TOTAL qualifier:

... TOTAL<Year, Quarter, Month> Sales)

[#3]

Thank you very much for your quick response! Calculations seems working correctly now.