How Can I do this set analisis?

[rcandeo]

I have a table with Month, Year and Amount of stocks. I want to show only the amount of the last year and month selected. Ag: If user selects all months of 2008, I want to show only the amount of december.

If I fix the month like above, it works. But How can I make this calculation selecting the last month/year?

sum({$<Month={12}, Year={2008}>} Amount)

[#2 · Accepted solution]

Will this work?

sum({$<Month={'$(=Max(Month))'}, Year={'$(=Max(Year))'}>} Amount)

I'm thinking the single-quotes are unnecessary, but it may depend on your data types.

[#3]

NMiller's suggestion should work. Use

...Year={$(=Max(Year)-1)}....

to get the previous year, as you mentioned.

[rcandeo]

using the expression sum({$<Month={'$(=Max(Month))'}, Year={'$(=Max(Year))'}>} Amount), didn't work cause I need to respect the selection.

If is selected Month april, may and june, I need to get june that is the Maximum month selected, but the expression got december month that is the max month of the table.

[#5]

To have set analysis disregard any current selections you can substitute the leading $ for a 1, like this

Sum( {1<Month... instead of Sum( {$<Month.....

This method disregards ANY selections you have made. Alternatively, if you are wanting to disregard only the selection on month, but still allow the aggregation set to be narrowed by other selections you can do something like this:

Sum( {$<Month={$(=Max( {1} Month) )}, Year={$(=Max(Year)-1)}>} Amount)

You can play around with the set modifiers to get what your looking for. If you only want the max over a selection, then use the $. If you want the Max regardless of the selection, use the 1. Hopefully this helps.

sjprows

[#6]

That string will respect the selection. If nothing is selected, the maximum month will be used. If anything is selected, then the maximum selected month will be used.

Take a look at this example. In my example, the months are numbers, so no single quotes are necessary.

[rcandeo]

The problem was that month field was not numeric, and in some months (I don't know the cause) were not working. Changing to numeric was the solution. Thank You all.

[#8]

Hi NMiller

Using your example, what if you want to list the calendar Quarters (report dimension) but consider in the stock evaluation expression only the last month of eack Quarter?