set analysis (max year and last year)

[#1]

Hi,

There something wrong in expressions I made:

This year sales amount:

    Sum({$<Year={$(=Max(Year))}>}[Sales Amount])

Last year sales amount

    Sum({$<Year={$(=Max(Year)-1)}>}[Sales Amount])

Sales amount is the same for both of them and it shouldn't be. And year is not max(year). Actually, this just gives a sum of all years or sum of selected year(s).

There was ei trigger on sheet (on opening) where max(year) was automatically selected, but I removed that.

What's wrong in the expressions?

[maxgro]

From what you wrote, same amount for max(year) and max(year) -1),

it seems your set modifiers (Year={$(=Max(Year)-1)}) doesn't work

Maybe your field isn't Year (year?) or the field is Year but you don't have an association between Year (the table with Year field) and Sales Amount (the table with ...) 

[#3]

Sum({$<Year={'2015'}>}[Sales Amount]) - expression works, but (Year={$(=Max(Year))}) doesn't. There is no calendar at all. Year field is in "fact" table and that's what I am using.

[maxgro]

remove the label of your expression (with max(Year)), go over the label with the mouse and look at the value you get for Max(Year) to check if you get something

or post a small example of your problem

(this can be useful to reduce and protect the data Preparing examples for Upload - Reduction and Data Scrambling)

[erjohnso]

Have you tried using max(Year) and max(Year,2) which calls the rank function instead of relying on the calc of Year-1?

[lars_plenge]

This is working for me

last year:

Calender.Year = {$(#=Only(Num(Calender.Year)-1))

current year:

Calender.Year = {$(#=Only(Num(Calender.Year)))

[#7]

hello ville,

  make use of this expression to get max year sales

=sum({<year={"$(=max({<year>}year))"}>}[sales revenue])

regards

Ashok

[jim_chan]

Sunny,

what if the same expression , and i need to add in max(Month) as well?

count({$<Year={'$(=Max(Year))'}>}ref_number)

Rgds,

Jim