Set Analysis Expression Not Working

[Anonymous]

What is wrong with my expressions?

why does it show total sales for the entire model?

I can use YearMonth for the Max Month, and it works fine, but I can't for the Prev Month Expression, because it is looking for last year and my model is only for a single year.

What can I do??

MAX MONTH

sum({$<Month={$(=Max(Month))},

Year,

Quarter,

Month>}Sales)

PREVIOUS MONTH

sum({$<Month={$(=Max(Month)-1)},

Year,

Quarter,

Month>}Sales)

[sivarajs]

i guess that is working can you explain somehow

[#3]

Hi,

If you select MAY, When you use:

$(=Max(Month)-1), you are getting 4 and Set analysis is waiting for "Apr"

You should use a function that returns month, not number. this happens because the -1 forces a math function with numeric result.

Try this:

Month={$(=Month(AddMonths(%_DateKey,-1)))}

If i were you, I would not use Year and Month separately, but together with MonthYear().

If you do not do this, you will have some troubles while comparing january (could be being compared with NEXT december, not previous).

I Hope this helps you!

[Anonymous]

the field YearMonth works for the current year,

but if I use the field Month, it shows all sales in the model

Not really sure what else to explain. I guess I don't know why The field month is showing total sales for the model and not total sales for the specific month.

and I can't use yearmonth for previous month, because it's looking for the previous year

[Anonymous]

diealgon,

Not sure I completely understand what you are suggesting.

are you saying i should use the following expression you suggested directly in the definition of the chart??

Month={$(=Month(AddMonths(%_DateKey,-1)))}

[#6]

No,

The complete expression in the def of the chart would be like this:

PREVIOUS MONTH

sum({$<Month={$(=Month(AddMonths(%_DateKey,-1)))},

Year,

Quarter,

Month>}Sales)

[Anonymous]

the expression

=sum({$<Month={$(=Month(AddMonths(%_DateKey,-1)))},

Year,

Quarter,

Month>}Sales)

is returning all sales that exist in the model

[oscar_ortiz]

Chris, Do you need to remove the second Month that you have designated in your Set analysis expression?  There is no reason to have that in there twice.

[#9]

Ok, there was an extra Month= in the Set analysis.

The complete expression in the def of the chart would be like this:

PREVIOUS MONTH

sum({$<Month={$(=Month(AddMonths(%_DateKey,-1)))},

Year,

Quarter>}Sales)

[#10]

That should work, but this one is better:

sum({$<Month={$(=Month(AddMonths(Max(%_DateKey),-1)))}, Year, Quarter>} Sales)