Set analysis < max(YearMonth)

[#1]

Hi,

Question about set analsis.

I wonder if it's possible to select every sales record smaller then the max selected year and month but keep the possibility to make selections in the dimension (the dimension contains projectnumbers).

For example, when I have the years 2010 and 2011 and months may and june selected it should sum all sales <= 201106 .

Using:

sum(

{1<

YearMonth={$(=max(YearMonth))}

Sales)

Will get the desired result but I will lose the flexibility to select any projects.

Using an aggr isn't working either:

sum(aggr(

sum(

{1<

YearMonth={$(=max(YearMonth))}

Sales)

,Project)

It give strange results. Anyone know how to fix this problem? An example is attached.

[#2]

Hi,

if u try this?

=sum( {<YearMonth = {"<=$(#=max(YearMonth))"}>} sales)

C u,

Stefano.

[#3 · Accepted solution]

And this:

sum( {$<YearMonth = {"<=$(#=max(YearMonth))"},Year,Month,Quarter>} sales)

if you don't want that year,month,quarter interfree.

Stefano

[#4]

Thanks Stefano. Works great 🙂 . If I understand correct the # sign returns the selection in relation to the current selection?

[#5]

Hi,

maybe it works without # also.

$ returns the selection in relation to the current selection