Straight Table --> Calculating maxMonth Sales per Year

[marcel_olmo]

Hi guys!

I just want to ask if is it possible to get in a straight table with the sales of the last month of each year.

I have the following table :

And I've calculated the sales of the maximum month with this formula :

sum({$< AsOfMonthDisplay={'dic'}>} [Asiento Importe])

I've been trying with =max(field), max(total field), max(all field), with aggregates, and I still don't get the maximum month per year.

I know we are now in december, but I only have data until november 2010. And with the following years, it would be interesting to get the maximum month per year everytime I want.

Anybody knows how to get this??

Many thanks in advance!!

[marcel_olmo]

What I've recently done is to get the month value and do the same, but with no results. 😞

turn monthname to monthNumber with num(month(date#(left(MonthName,3),'MMM')))

and if I do the same as before "statically", I get the same results :

sum({$< AsOfMonthDisplayNum={'12'}>} [Asiento Importe])

I've been trying =max(field), =max(total field), etc. but with no results.

Anyobody knows why?

Many thanks in advance.

[johnw]

You could tie the AsOfYear directly to the Month in question, assuming you want the maximum month available, not the maximum month selected. Maybe something like this:

AsOfYear, Month
2005, 12
2006, 12
...
2009, 12
2010, 11

Loaded something like this:

MaxMonthsPerYear:
LOAD
AsOfYear
,max(Month) as Month
RESIDENT SomeCalendar
;

However, this is probably overly simplistic if you already have an AsOf table. You might need to integrate this better with the rest of the model to prevent problems, and I don't know the rest of the model.

There might be a better solution. I can't put my finger on it, but something doesn't feel right about what I'm saying.

[marcel_olmo]

Thanks again John, it was a nice idea, now it supposed to work, but I still don't know why it's not working.

I've created two more fields :

AsOfMonthDisplayNum -> (the number of the month)

AsOfMonthDisplayNumMax -> (the maximum number of month per year)

And I've added AsOfMonthDisplayNumMax to the straight table, to check that everything it seems to be right.

What I've changed to get the sales maximum Month is :

sum({$<AsOfMonthDisplayNum ={"=AsOfMonthDisplayNumMax"} >} [Asiento Importe])

So it's supposed to assign the max number of month to the number of the month in the set analysis. But it still doesn't work for the last year!!!

Why it's still not working?

Anybody could come up with the reason??

Many thanks in advance!!!