Set analysis - Not returning desired result

[adurgani]

This is my formula:

Sum({$<Dimension={'Actual'},[Month]={'January','February'}>}[Line Amount]) + Sum({$<Dimension={'April Forecast'},[Month]={'March','April','May','June','July','August','September','October','November','December'}>}[Line Amount])

When I use the month listbox and filter to January I still get the full amount and it doesn't show me just January. How shall I amend the formula to get the desired result?

[tomasz_tru]

And what is the desired result?

[adurgani]

For the data to filter by month. When I select January it still shows me the full year total but I want it to show totals that match selections.

[vishsaggi]

Can you post a snapshot of your data model or some sample data and your expected output?

[adurgani]

Table1:
Dimension	Jan	Feb	Mar	Apr	May
Forecast	20	50	30	40	50

Table 1 is loaded as a cross table with month and Amount

Table 2:
Dimension	Month	Amount
Actuals	Jan	10
Actuals	Feb	20

Table 2 is concatenated into table 1

Desired result is total of all months that don't have an actual (Mar, Apr and May) and Actuals (Jan,Feb). This should be filtered by month as needed. See below for month by month

Jan = 10

Feb = 20

Mar = 30

Apr = 40

May = 50

[vishsaggi]

So what is the Total amount you are looking for? is it 100 or 120?

Try these not sure if this is what you are looking for

CrossTab:
CrossTable (Month, LineAmount,1)
LOAD * INLINE [
Dimension, Jan, Feb, Mar, Apr, May
Forecast, 20, 50, 30, 40, 50
];

LOAD * INLINE [
Dimension, Month, LineAmount
Actuals, Jan, 10
Actuals, Feb, 20
];

= Sum({< Month -= P({<Dimension = {'Actuals'} >} Month) >} LineAmount)

OR

= Sum({< Month = P({<Dimension = {'Actuals'} >} Month) >} LineAmount)

[adurgani]

Thanks so much!

The total in this care is 150. I tried to use the formulas but they aren't working for some reason. What's wrong with this formula

=sum({<Dimension&Month={'ActualsJanuary','ActualsFebruary','ForecastMarch','ForecastApril','ForecastMay'}>}LineAmount)

[vishsaggi]

you cannot use Dimension&Month like that in your set analysis. based on my data set send me the script your are running. 150? How did you get this total and what are the selections you made?

[adurgani]

If the formula worked it would give me a total of 150. see highlighted rows for what's included by month in the 150.

Can I somehow create a concated field the combines month and dimension?

[vishsaggi]

Try like

CrossTab:
CrossTable (Month, LineAmount,1)
LOAD * INLINE [
Dimension, Jan, Feb, Mar, Apr, May
Forecast, 20, 50, 30, 40, 50
];


NoConcatenate
Final:
LOAD *, Dimension&'|'&Month AS DimMonth
Resident CrossTab;

Drop Table CrossTab;

LOAD *,Dimension&'|'&Month AS DimMonth INLINE [
Dimension, Month, LineAmount
Actuals, Jan, 10
Actuals, Feb, 20
];

And use your expr

=sum({<DimMonth={'Actuals|Jan','Actuals|Feb','Forecast|Mar','Forecast|Apr','Forecast|May'}>}LineAmount)